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

0
votes

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 App\Category 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!

2
laravel

2 Answers

1
votes

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.

0
votes

Here is the solution I came up with:

public static function getTotalShirts()
{
    $category = Category::where('slug', 'shirts')->first();
    $children = $category->children()->get();
    $count = 0;
    foreach ($children as $child) {
        $count += $child->products()->count();
    }
    return $count;
}

I was wondering if there was some neater way to do it? And is having public static functions safe?