php - Get products count which are under sub categories of a parent category in laravel

I am trying to display all the categories with their products_count. I am using eloquent to get categories and their individual products_count without any problem. But as my categories are nested and I thought it would be great if I can sum all of its subcategories products count as parent category products_count.

This is my categories json output

{
            "id": 3,
            "name_en": "Spices",
            "parent_id": null,
            "products_count": 0, // it should be 1 instead of 0
            "children_count": 4,
            "children": [
                {
                    "id": 4,
                    "name_en": "HIJ",
                    "parent_id": 3,
                    "products_count": 0,
                    "children_count": 0,
                    "children": []
                },
                {
                    "id": 5,
                    "name_en": "XYZ",
                    "parent_id": 3,
                    "products_count": 1,
                    "children_count": 0,
                    "children": []
                },
                {
                    "id": 6,
                    "name_en": "ABC",
                    "parent_id": 3,
                    "products_count": 0,
                    "children_count": 0,
                    "children": []
                },
                {
                    "id": 7,
                    "name_en": "DEF",
                    "parent_id": 3,
                    "products_count": 0,
                    "children_count": 0,
                    "children": []
                }
            ]
        }
Copy code

Here is my categoryController

...
public function index() {
   return Category::whereNull('parent_id')
            ->with(['children' => function($category) {
                return $category->withCount(['children', 'products']);
            }])
            ->withCount(['children', 'products'])
            ->get()
}
...
Copy code

CategoryModel

class CategoryModel extends Model {

    public function parent() {
       return $this->belongsTo('App\Category', 'id', 'parent_id');
    }

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

    public function products() {
        return $this->belongsToMany('App\Product')->withTimestamps();
    }
}
Copy code

ProductModel

class Product extends Model
{
    public function categories() {
        return $this->belongsToMany('App\Category')->withTimestamps();
    }
}
Copy code

EDIT

category_product pivot table has two columns:

product_id | category_id

the parent category has no direct relation with its subcategory products. Is it possible to get this done with an eloquent query? As I am exploring some similar questions, people have tried it with a recursive relationship, and some are trying with the manual for each loop and adding product counts of subcategories by looping through parent categories. I thought this is an expensive approach. There must be optimal way to do it. Please suggest me what could be the best way to deal with this.

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.