How to save and retrieve any level of nested categories in MySQL with Laravel and Eloquent?

In this post I wanted to share a simple solution to save any level of nested categories in a MySQL database table and retrieve them using Laravel Eloquent models and their relationships. This means that you’ll be able to store and retrieve any number of categories > subcategories > sub subcategories… (you get the idea) using one database table.

Although this post uses categories as the example you can do the same for any other resource that you need to store in a database with nested levels of hierarchy.

Database table structure

Imagine we have a categories table in the database that looks like this:

id	name	parent_category_id
1	Restaurants	null
2	Games	null
3	Italian	1
4	Pizzas	3
5	Strategy	2

We can guess the hierarchy from the above table but we now want to be able to retrieve that information in a hierarchical way so it will look like this:

- Restaurants
    - Italian
        - Pizzas
- Games
    - Strategy

Laravel Migration

First we’ll have to create the Laravel migration to create the categories table in the database, we can do that by simply running the following command:

php artisan make:migration create_categories_table

This will generate the migration file and we’ll add the structure of our table like this:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCategoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            // Fields
            $table->bigIncrements('id');
            $table->string('name');
            $table->bigInteger('parent_category_id')->unsigned()->nullable();

            // Timestamps
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('categories');
    }
}

Then you need to run the migrations with the following command:

php artisan migrate

If you notice we added a nullable parent_category_id in the categories table, this column will be null whenever the category is a first-level category and it will contain a category id (references the id of the categories in the same table) whenever that category is a child of another category.

Laravel Eloquent Model

Next we’ll need to create a Laravel Eloquent Model with the following command:

php artisan make:model Category

We will add the Eloquent relationships needed to be able to easily retrieve the children or parents categories recursively from any category and our model will look like this:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    // Recursive children
    public function children() {
        return $this->hasMany('App\Category', 'parent_category_id')->with('children');
    }

}

If you look closely, besides from adding the normal $this->hasMany() relationship in the children method we are adding a with('children')method at the end, this little trick will help us get the categories recursively.

In the above example I only added the children relationship method to get all the children recursively but if you need a more complete version take a look at the following model where I defined the methods for bringing just a direct child and also the inverse relationships for the parent and parents categories.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    // One level child
    public function child() {
        return $this->hasMany('App\Category', 'parent_category_id');
    }

    // Recursive children
    public function children() {
        return $this->hasMany('App\Category', 'parent_category_id')->with('children');
    }

    // One level parent
    public function parent() {
        return $this->belongsTo('App\Category', 'parent_category_id');
    }

    // Recursive parents
    public function parents() {
        return $this->belongsTo('App\Category', 'parent_category_id')->with('parent');
    }

}

Retrieving nested categories

The last step is to retrieve the categories from the database using our recently created Eloquent Model. Let’s say we have a CategoryController and we want to return a JSON response with all the categories nested appropriately according to their hierarchy.

To do so we’ll use the with('children') method in the query performed with the Category model to instruct Laravel to eager load the children relationship. And because inside the children relationship in the Category model we are also instructing to eager load the children relationship, we created the recursion we need to achieve our goal.

Our CategoryController would look something like this:

<?php

namespace App\Http\Controllers;

use App\Category;
use Illuminate\Http\Request;

class CategoryController extends Controller
{
    public function getCategories()
    {
        $categories = Category::with('children')
            ->whereNull('parent_category_id')
            ->get();

        return response()->json($categories);
    }
}

We added the whereNull('parent_category_id') to make sure we are only bringing first-level categories first and with the eager loading functionality we make sure all of the children categories will also be obtained and structured under the first-level categories.

I hope this article helped you but if you still have some questions or suggestions please leave it in the comments and I’ll answer as soon as possible. Your feedback is welcomed.