Reputation: 1276

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

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?

Upvotes: 8

Answers (6)

Danila Popov

Reputation: 1

I think the better way is article Eloquent: Recursive hasMany Relationship with Unlimited Subcategories

Just add products relationship to the Category Model

Class Category extends Model

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

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

 public function products()
 {
     return $this->hasMany(Product::class);
 }

Query:

 $Categories = Category::whereNull('parent_id')
->with('childrenCategory','products')
->get();

Returns:

[
{
    "id": 1,
    "parent_id": null,
    "name": "Category 1",
    "description": "Category 1",
    "created_at": "2021-12-10T11:43:48.000000Z",
    "updated_at": "2021-12-10T11:43:48.000000Z",
    "products": [
    {
        "id": 1,
        "category_id": 1,

        "name": "productNobis provident.",
        "description": "Ad quia ullam voluptatem modi ab dolorem non.",
        "qty": 1,
        "created_at": "2021-12-10T11:43:48.000000Z",
        "updated_at": "2021-12-10T11:43:48.000000Z"
    },
    ],
    "children_categories": [
    
    ]
},
{
    "id": 2,
    "parent_id": null,
    "name": "Category 2",
    "description": "Laborum aut quibusdam earum commodi.",
    "created_at": "2021-12-10T11:43:48.000000Z",
    "updated_at": "2021-12-10T11:43:48.000000Z",
    "products": [
    {
        "id": 5,
        "category_id": 2,
        "name": "productVoluptas laborum adipisci.",
        "description": "Autem ut et voluptate ab sit voluptatem quia.",
        "qty": 1,
        "created_at": "2021-12-10T11:43:48.000000Z",
        "updated_at": "2021-12-10T11:43:48.000000Z"
    },
    ],
    "children_categories": [
    {
        "id": 4,
        "parent_id": 2,
        "name": "Category 4",
        "description": "Eos nemo libero non eius voluptates.",
        "created_at": "2021-12-10T11:43:48.000000Z",
        "updated_at": "2021-12-10T11:43:48.000000Z",
        "products": [
        {
            "id": 9,
            "category_id": 4,
            "name": "productSunt deleniti minus.",
            "description": "Vitae numquam autem consequuntur consequuntur.",
            "qty": 1,
            "created_at": "2021-12-10T11:43:48.000000Z",
            "updated_at": "2021-12-10T11:43:48.000000Z"
        },
        ],
        "categories": [
        {
            "id": 6,
            "parent_id": 4,
            "name": "Rerum qui earum.",
            "description": "Nihil ipsum officiis vitae et error accusamus ipsam.",
    
            "created_at": "2021-12-10T11:43:48.000000Z",
            "updated_at": "2021-12-10T11:43:48.000000Z",
            "products": [
            {
                "id": 13,
                "category_id": 6,
        
                "name": "productConsequatur quisquam molestiae.",
                "description": "Autem sed mollitia eos eveniet iste ipsa aut aliquam.",
                "qty": 1,
                "created_at": "2021-12-10T11:43:48.000000Z",
                "updated_at": "2021-12-10T11:43:48.000000Z"
            },
        ]
        }
        ]
    }
    ]
}
]

Upvotes: 0

IT Study with hasan

Reputation: 11

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'));
}

Upvotes: 1

Mohammed F. Ouda

Reputation: 266

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))
        )");
    });
}

Upvotes: 1

agamyrat_akmyradov

Reputation: 19

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

and in view your can display items with foreach loop

Upvotes: 3

Foued MOUSSI

Reputation: 4813

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);

Upvotes: 6

Rwd

Reputation: 35170

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)

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

Add the HasRecursiveRelationships trait to your Category model:

use Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

class Category extends Model
{
    use HasRecursiveRelationships;

    ...
}

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.

Upvotes: 4

Laravel get all products from parent category and all it&#39;s sub-categories

Answers (6)

Related Questions

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