I am trying to count all active products in parent category. A parent category has many child categories(only one level deep). A product can be assigned to multiple child categories but not parent category.
For ex. I have "Toys"(parent) category having 3 child categories "for boys"(having 5 products), "for girls" (having 6 products), "for teens" (having 12 products).
My sidebar menu at homepage have all the parent categories and wants to show product count of respective parent category using eloquent way. The eloquent query is being used in view composer.
Using above example, I want to show like:
Toys 23 (5 + 6 + 12 = 23 after totaling products from child categories)
Games 10
Sports 15
and so on..
I want to get parent category with product count, and not child categories details.
My Category.php Model
public function parent() {
return $this->belongsTo(self::class, 'parent_id');
}
public function children() {
return $this->hasMany(self::class, 'parent_id', 'id');
}
public function products() {
return $this->belongsToMany('App\Product');
}
My Product.php Model
public function categories() {
return $this->belongsToMany('App\Category');
}
public function user() {
return $this->belongsTo('App\User');
}
Queries I have tried
Category::select('id', 'parent_id', 'slug' ,'name')->where(['active' => 1, 'parent_id' => null])->with(['children' => function($query) {
$query->withCount('products');
}])->get();
This give me product count of children. I can loop through children and can add total product for parents. But, doesn't seems me a good way, since this loads child categories data too which I don't need. I have a total of around 400 categories.
Category::select('id', 'parent_id', 'slug' ,'name')->where(['active' => 1, 'parent_id' => null])->with('children')->withCount('products')->get();
This gives product count as 0, obviously as parent category has no product assigned to it.
I see a lot of examples her using withCount, but they all explains hasManyThrough relations. I am having belongsToMany relation set up in Category and Product Model. Guide me if withCount is implemented in belongsToMany relation anywhere.
I am looking for optimized database queries to get the result I need. Please let me know your thought.
Thanks a lot for your valuable time.