Laravel + Eloquent: Store categories and subcategories in the same table

Introduction

It’s common to encounter scenarios where items are organized in categories which themselves can have subcategories, forming a tree-like structure. In a traditional database, you might consider using two tables: one for categories and another for subcategories. However, this approach can result in complex queries and extensive joins when you have multiple levels. An alternative and more efficient approach in terms of querying and data management is to use a self-referencing table.

Laravel with Eloquent offers an elegant ORM that can help us handle these relationships with ease, ensuring simplicity and readability without sacrificing functionality.

Prerequisites

• Basic understanding of Laravel framework and MVC concepts.
• PHP and Composer installed on your development machine.
• Laravel framework installed and a new Laravel project created.
• MySQL or any other Laravel supported database service running.

Setting Up the Database

First, you need to configure your database connection in .env file. Once done, we will create a migration file for our categories table. Run the following Artisan command to create the migration:

php artisan make:migration create_categories_table --create=categories

Next, define the table schema within the migration file located in database/migrations:

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->unsignedBigInteger('parent_id')->nullable();
    $table->foreign('parent_id')->references('id')->on('categories')->onDelete('set null');
    $table->timestamps();
});

This migration creates a table with an optional self-referencing parent_id column that allows rows in the same table to reference one another, establishing parent-child relationships. After defining the schema, you can run the migration:

php artisan migrate

The Category Model

To interact with our table using Eloquent, we will create a Category model by running the command:

php artisan make:model Category

In the app/Models directory, you will now find the Category model file. Here, define the relationship to itself using Eloquent’s
elations:

class Category extends Model
{
    public function parent()
    {
        return $this->belongsTo(Category::class, 'parent_id');
    }

    public function children()
    {
        return $this->hasMany(Category::class, 'parent_id');
    }
}

This defines a parent category having many children, and each child category belonging to a parent. A root category (a category without a parent) will have parent_id set to null.

Seeding the Categories Table

We can use seeders to populate our categories table with some test data. Run the following command to create a seeder:

php artisan make:seeder CategoriesTableSeeder

In the created seeder file in database/seeders, you can insert categories:

public function run()
{
    // Create parent categories
    $parent1 = Category::create(['name' => 'Parent Category 1']);
    $parent2 = Category::create(['name' => 'Parent Category 2']);

    // Create child categories
    $child1 = Category::create(['name' => 'Child Category 1', 'parent_id' => $parent1->id]);
    $child2 = Category::create(['name' => 'Child Category 2', 'parent_id' => $parent1->id]);
    $child3 = Category::create(['name' => 'Child Category 3', 'parent_id' => $parent2->id]);
}

To seed the database, run the seeder via Artisan:

php artisan db:seed --class=CategoriesTableSeeder

Retrieving Categories and Subcategories

To access parent categories and their children, you can write:

$parentCategories = Category::whereNull('parent_id')->with('children')->get();

If you want to retrieve a category along with its subcategories to any depth, you can use a recursive relationship method that Eloquent doesn’t provide out of the box. Define a new method in the Category model:

public function childrenRecursive()
{
    return $this->children()->with('childrenRecursive');
}

This will enable you to load all descendants and not just immediate children:

$parentCategories = Category::whereNull('parent_id')->with('childrenRecursive')->get();

Conclusion

In this tutorial, you’ve seen how to manage a hierarchy of categories and subcategories using Laravel and Eloquent. By utilizing a single table with a self-referencing column and defining appropriate relationships, we were able to structure the categories effectively. This method can simplify queries and make it easier to maintain the data as it grows in depth and complexity.

With your new knowledge, you can now implement robust category structures in your next Laravel project, enhancing both your application’s design and functionality. Remember that Eloquent’s relation system is highly flexible, and with a bit of creativity, you can solve various data structure problems in a clean and efficient way.