How to get data from multilevel categories in Laravel 9

First, let’s create a new migration for the categories table. You can use the following command to generate a migration file:

php artisan make:migration create_categories_table --create=categories

Then, in the up method of the migration file, define the schema for the categories table:

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->integer('parent_id')->unsigned()->nullable();
        $table->timestamps();
    });
}

Here, we have defined the name column to store the name of the category, the parent_id column to store the ID of the parent category (if any), and the usual created_at and updated_at columns.

Next, let’s create the Category model. You can use the following command to generate the model file:

php artisan make:model Category

Then, in the Category model, define the relationships with itself (for subcategories) and with the Ad model (for filtering):

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    use HasFactory;

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

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

    public function ads()
    {
        return $this->hasMany(Ad::class);
    }
}

Here, we have defined two relationships with itself: children to get the subcategories and parent to get the parent category (if any). We have also defined a relationship with the Ad model using the hasMany method.

Finally, to filter the Ad model by category or subcategory, you can use the following code:

$category = Category::findOrFail($categoryId);
$ads = $category->ads()->with('category')->get();

Here, we first retrieve the category object using the $categoryId. Then, we use the ads relationship to get all the ads related to that category (and its subcategories) using the with method to eager load the related category for each ad. The result is a collection of Ad objects that you can use for further processing.

To retrieve ads from both the parent category and its subcategories, you can use Laravel’s whereHas method in your query. Here’s an example code:

$category = Category::findOrFail($categoryId);
$ads = Ad::whereHas('category', function ($query) use ($category) {
        $query->where('id', $category->id)->orWhere('parent_id', $category->id);
    })->get();

Here, we first retrieve the $category object using its ID. Then, we use the whereHas method on the Ad model to filter the ads based on their category relationship. The whereHas method takes a closure that defines the condition for the relationship. In our case, we check if the ad’s category ID is equal to the $category ID or if the ad’s category parent ID is equal to the $category ID. This will retrieve ads that belong to the $category itself as well as its subcategories.

The result is a collection of Ad objects that you can use for further processing.

To filter all ads in the Real Estate main category (Real Estate > Residential > Houses), you can use Laravel’s whereHas method with nested closures to filter based on the category and its subcategories. Here’s an example code:

$category = Category::where('name', 'Real Estate')->firstOrFail();
$ads = Ad::whereHas('category', function ($query) use ($category) {
        $query->where('id', $category->id)->orWhere(function ($query) use ($category) {
            $query->where('parent_id', $category->id)->orWhere(function ($query) use ($category) {
                $query->where('parent_id', function ($query) use ($category) {
                    $query->select('id')->from('categories')->where('parent_id', $category->id);
                });
            });
        });
    })->get();

Here, we first retrieve the $category object for the Real Estate category using its name. Then, we use the whereHas method on the Ad model to filter the ads based on their category relationship. We use nested closures to define the conditions for the category and its subcategories. Here’s how the nested closures work:

• The first closure checks if the ad’s category ID is equal to the $category ID.
• The second closure checks if the ad’s category parent ID is equal to the $category ID.
• The third closure checks if the ad’s category grandparent ID (i.e., the parent of the parent) is equal to the $category ID. We use a subquery to retrieve the parent categories of the $category and then use the ID of the first parent category as the condition.

This will retrieve ads that belong to the Real Estate category and its subcategories, including Residential and Houses.

The generated MySQL query may look like this:

select
    *
from
    `ads`
where
    exists (
        select
            *
        from
            `categories`
        where
            `ads`.`category_id` = `categories`.`id`
            and (
                `categories`.`id` = 1
                or (
                    `categories`.`parent_id` = 1
                    or exists (
                        select
                            *
                        from
                            `categories`
                        where
                            `categories`.`parent_id` = 1
                            and `categories`.`parent_id` in (
                                select
                                    `id`
                                from
                                    `categories`
                                where
                                    `parent_id` = 1
                            )
                    )
                )
            )
    )

In this query, the whereHas method generates a subquery that checks if an ad’s category_id matches the id of the Real Estate category or any of its subcategories up to 2 levels deep. The subquery is then used in the exists clause of the main query to filter the ads based on their category relationship.