Laravel get all products from parent category and all it's sub-categories

Question

I'm trying to retrieve products from a category and all it's sub-categories.

Here's my categories table :

| id    | parent_id     | name          |
|----   |-----------    |-------------  |
| 1     | NULL          | Electronics   |
| 2     | 1             | Computers     |
| 3     | 2             | Accessories   |
| 4     | 3             | Keyboards     |

and here's my products table :

| id    | category_id   | name          |
|----   |-------------  |-----------    |
| 1     | 2             | Product 1     |
| 2     | 3             | Product 2     |
| 3     | 4             | Product 3     |

Let's say i'm in Computers category page, and i want to display products from this table and all it's childrens.

so it should get products first from Computers and Accessories and also Keyboards.

Here's my Category Model :

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

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

public function products() {
    return $this->hasManyThrough(Product::class, Category::class, 'parent_id', 'category_id', 'id');
}

Product Model :

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

Query :

Category::with(['products', 'childs.products'])->where('id', $category->id)->get();

Return :

{
    "id":11,
    "parent_id":4,
    "name":"Computers",
    "products":[
        {
            "id":2,
            "category_id":12,
            "title":"Product 1",
            "laravel_through_key":11
        }
    ],
    "childs":[
        {
            "id":12,
            "parent_id":11,
            "name":"Accessories",
            "products":[
                {
                    "id":1,
                    "category_id":13,
                    "user_id":1,
                    "title":"Product 2",
                    "laravel_through_key":12
                }
            ]
        }
    ]
}

Above, it's escaping the last child category Keyboards.

I have tried to use hasManyThrough relationship but i only got products from Computers and Accessories but didn't reach to Keyboards.

So if i'm on a category i want to get all products from this category tree. even if a sub-category has sub-categories.

How can i achieve this?

Thanks.

Update :

i applied the snippet in Foued MOUSSI's answer :

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

$categoryIds = Category::with('childrenRecursive')->where('id', $category->id)->get();

Return :

[
    {
        "id":2,
        "parent_id":1,
        "name":"Computers",
        "children_recursive":[
            {
                "id":3,
                "parent_id":2,
                "name":"Accessories",
                "children_recursive":[
                    {
                        "id":4,
                        "parent_id":3,
                        "name":"Keyboards",
                        "children_recursive":[]
                    }
                ]
            }
        ]
    }
]

and i got the array of category and all it's sub-categories, but to get products from all these categories i need to extract the IDs from the the list with childrenRecursive to call something like :

Product::whereIn('category_id', $categoryIds)->get();

Any idea?

Solution 1

You may fix it by :

make a recursive relation: (Please Refer to Alex Harris answer here)

// recursive, loads all descendants
// App\Category
public function childrenRecursive()
{
   return $this->childs()->with('childrenRecursive');
}

$data = Category::with(['products', 'childrenRecursive', 'childrenRecursive.products'])->where('id', 2)->get()->toArray();

#Edit: Extract list of products

Define Flatten laravel recursive relationship collection (tree collections) function inside your controller

public function flatten($array)
{
        $flatArray = [];

        if (!is_array($array)) {
            $array = (array)$array;
        }

        foreach($array as $key => $value) {
            if (is_array($value) || is_object($value)) {
                $flatArray = array_merge($flatArray, $this->flatten($value));
            } else {
                $flatArray[0][$key] = $value;
            }
        }

        return $flatArray;
}

Then in order to only have products item

$data = Category::with(['products', 'childrenRecursive', 'childrenRecursive.products'])->where('id', 2)->get()->toArray();

$flatten = $this->flatten($data);

foreach ($flatten as $key => $fl) {
    // eliminate categories from $flatten array
    if (!array_key_exists('category_id', $fl)) {
        unset($flatten[$key]);
    }
}

$product = array_values($flatten);

Solution 2

One option would be to use something like laravel-adjacency-list. This would allow you to use CTE to recursively load the relationships.

Below are the steps to get you set up (at the time of writing)

1. Run composer require staudenmeir/laravel-adjacency-list:"^1.0"

2. Add the HasRecursiveRelationships trait to your Category model:

   use Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
   
   class Category extends Model
   {
       use HasRecursiveRelationships;
   
       ...
   }
   

3. Change your query to be:

   Category::with('descendants.products')->where('id', $id)->first(); //$id being the id of the parent category you want to get.
   

---

If you're wanting to just get the products that are in/under a category you could do something like:

Product::whereHas('category', function ($query) use ($category) {
    $query->whereIn('categories.id', $category->descendantsAndSelf()->select('id')->getQuery());
})->get();

For more information on how to use laravel-adjacency-list please refer to the documentation.

Solution 3

$categories  = Category::whereParentId(null)->with('children.children.children')->get();

and in view your can display items with foreach loop

Solution 4

Since I'm using MySQL 5, I used this way to resolve this issue in my project if anyone is interested about it. It makes more sense for me.

If you're using MySQL 8, you can take this question as a reference to edit this code. How to create a MySQL hierarchical recursive query?

public function scopeWhereCategoryId($query, $category_id){
    return $query->where(function($query) use ($category_id){
        $query->where('category_id', trim($category_id));
        $query->orWhereRaw("category_id in (
            select  id
            from    (select * from pm_categories
                    order by category_id, id) categories_sorted,
                    (select @pv := '" . trim($category_id) . "') initialisation
            where   find_in_set(category_id, @pv)
            and     length(@pv := concat(@pv, ',', id))
        )");
    });
}

Solution 5

If you use ORM Eloquent Relationship

public function categoriesProduct(ProductCategory $category)
{
     $categories = ProductCategory::where('parent_id', $category->id)
                                  ->orWhere('id', $category->id)
                                  ->latest()
                                  ->get();
     return view('categoryProduct', compact('categories', 'category'));
}

View more solutions