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": []
}
]
}
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()
}
...
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();
}
}
ProductModel
class Product extends Model
{
public function categories() {
return $this->belongsToMany('App\Category')->withTimestamps();
}
}
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.