In this part, I'd like to focus on the backend side of our application and build the basic API for fetching categories and their related topics.

We'll start this part by connecting to the database and creating the needed migrations. Then, we'll prepare some seed data to load our database with. Then we'll prepare the data that should be sent to the client (our SPA). After that, we'll finish up by creating two API endpoints. One for fetching all categories, and the other to fetch all topics for each category.

Most of the content in this part should not feel new to you if you're already a Laravel developer (you are, right?).

Setting up a database connection

Of course, our first step here is to set up a database connection. For this demo we'll quickly set up a SQLite database.

Here's how.

First, go to your .env file and change the database connection from mysql to sqlite.

DB_CONNECTION=sqlite

Then create the database by creating an empty database.sqlite file in the database directory. And you're done!

The migrations

Our next step is to create the migrations for the categories & topics tables.

The categories table will basically have two fields: name and description — of course with the default id and timestamps fields.

So, create the migration — I'm using here a shortcut by creating the model with its migration at the same time.

php artisan make:model Category -m

Then go to the migration file and add the aforementioned fields:

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name')->unique();
        $table->string('description');
        $table->timestamps();
    });
}

Now, let's do the same for the topics table. It'll have a title, body, category_id, and views (for number of views).

So, create it:

php artisan make:model Topic -m

And add the fields:

public function up()
{
    Schema::create('topics', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('category_id')->unsigned();
        $table->string('title');
        $table->text('body');
        $table->integer('views')->default(0);
        $table->timestamps();

        $table->foreign('category_id')
            ->references('id')->on('categories')
            ->onDelete('cascade');
    });
}

Finally, migrate the database:

php artisan migrate

Setting up the relations

Obviously, our next step is to set up the relationship between both models. It's also obvious that the kind of relationship between them is 1-to-Many.

So, in the Category model you'd add this method:

public function topics()
{
    return $this->hasMany(Topic::class);
}

And for the Topic model you'd add this:

public function category()
{
    return $this->belongsTo(Category::class);
}

Seeding the database

To make our task in this project easier, we're going to seed the database with some random data — and luckily Laravel makes it so easy.

To do that, we first have to define the needed model factories in /database/factories/ModelFactory.php.

$factory->define(App\Category::class, function (Faker\Generator $faker) {
    return [
        'name' => implode(' ', $faker->words(2)),
        'description' => $faker->sentence(),
    ];
});

$factory->define(App\Topic::class, function (Faker\Generator $faker) {
    return [
        'category_id' => null,
        'title' => $faker->sentence,
        'body' => $faker->paragraph(7),
        'views' => $faker->numberBetween(0, 10000),
        'created_at' => $faker->datetimeBetween('-5 months'),
    ];
});

Next, create a seed class named CategoriesTopicsSeeder in the /database/seeds directory with this command:

php artisan make:seed CategoriesTopicsSeeder

Don't forget to run: composer dump-autoload afterwards.

Then, go to that seeder and put in the following code.

use App\Topic;
use App\Category;
use Illuminate\Database\Seeder;

class CategoriesTopicsSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Topic::truncate();
        Category::truncate();

        factory(Category::class, 5)->create()->each(function($c) {
            $c->topics()->saveMany(
                factory(Topic::class, 5)->create([
                    'category_id' => $c->id
                ])
            );
        });
    }
}

What this code does is create 5 categories with 5 topics for each one. And note that at the top we told it to clear the database each time we run the seeder; to prevent it from keeping the old seeded data.

Lastly, we have to register that seeder in our /database/seeds/DatabaseSeeder.php file.

public function run()
{
    $this->call(CategoriesTopicsSeeder::class);
}

Now you can seed the database with: php artisan db:seed. After that, your database should end up with 5 categories and 25 topics.

The data to be sent

When I'm building APIs, I like to determine what should be sent to the client rather than sending everything; that can be easily done with the $visible property.

Another great thing Laravel provides us with is the ability to add additional attributes that don't have corresponding column in the database. Which we can do using accessors and the $appends property.

Let's start with our Topic model.

For now, we only need to add one additional attribute called time. This attribute will contain the timestamp of the created_at field. We need it as a timestamp because in the future we're going to create a custom filter in Vue to convert it to something more readable.

We add it using the $appends property:

protected $appends = ['time'];

But for that to work we have to define the corresponding accessor.

public function getTimeAttribute()
{
    return $this->created_at->timestamp;
}

Finally, we have to determine what fields need to be visible in our json response.

protected $visible = ['id', 'title', 'body', 'views', 'time', 'category_id'];

After all, here's how your Topic.php file should look like:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Topic extends Model
{
    protected $visible = ['id', 'title', 'body', 'views', 'time', 'category_id'];
    protected $appends = ['time'];

    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function getTimeAttribute()
    {
        return $this->created_at->timestamp;
    }

}

For the Category model, we only need to add an additional field for showing the number of topics it contains. So, here's how it should look like:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    protected $visible = ['id', 'name', 'description', 'numberOfTopics'];
    protected $appends = ['numberOfTopics'];

    public function topics()
    {
        return $this->hasMany(Topic::class);
    }

    public function getNumberOfTopicsAttribute()
    {
        return $this->topics->count();
    }
}

If you want to check your work, you can open php artisan tinker and try to fetch something like the first topic using App\Topic::first()->toJSON(). If everything is working, move on to the next step.

Register endpoints

Our final step in this part is to create the two endpoints we talked about with the needed controller.

So, in your routes file, register these two endpoints at the top:

Route::get('/api/categories', 'CategoryController@index');
Route::get('/api/categories/{id}/topics', 'CategoryController@topics');

Then, create that CategoryController manually or using the command php artisan make:controller CategoryController.

Now, open it up and put in the following code:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Category;

class CategoryController extends Controller
{
    public function index()
    {
        return Category::all();
    }

    public function topics($categoryId)
    {
        return Category::findOrFail($categoryId)
            ->topics()
            ->orderBy('created_at', 'desc')
            ->get();
    }
}

If everything is OK, you should be able to request those endpoints from your browser. Here's an example of requesting all categories:

Our next step

After we have created the needed API endpoints for our client, we need to consume them.

So, in the next part, we'll switch back to Vue and see how we can display all categories with their related topics. And while we're doing that we'll touch on an important concept in vue-router called route transition.