laravel - How to get a total amount of products in children categories of single parent

Question

I have a Category model in which has children categories which has products... getting the total amount of products along with the children is easy. But what about getting the parent categories total amount which is the sum of all children categories of this single parent?

Here is the AppCategory model:

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

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

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

public function scopeTotalShirts($query)
{
    return $query->where('slug', 'shirts')->first()->children->each->products()->count();
}

A shirt would have child categories such as T-Shirt, Long Sleeve, Graphic, etc.

I want to get the total of those children categories so it all adds up in the Shirts (parent) category.

Is there some elegant way to do this besides querying up the children categories and getting the count?

Thank you!

question from:https://stackoverflow.com/questions/65867204/how-to-get-a-total-amount-of-products-in-children-categories-of-single-parent

Answer 1

Based on your answer you can remove the extra queries in the loop, at the least:

$children = $category->children()->withCount('products')->get();

return $children->sum('products_count');

Or you can do the actual query from the inverse direction and remove the need for all of this:

return Product::whereHas('child.category', function ($q) {
    $q->where('slug', 'shirts');
})->count();

Though, I don't know how you setup these relationships in the inverse.

---

Based on the relationships you have you probably need to do more querying here as I would suppose a Product could belong to a parent or child category:

return Product::whereHas('category', function ($q) {
    $q->where('slug', 'shirts')
        ->orWhereHas('parent', fn ($q) => $q->where('slug', 'shirts'));
})->count();

The Product model should have a category relationship (belongsTo). The parent relationship on Category should be a belongsTo not hasOne.