Laravel total products count from childs in parent category

Question

I have eloquent relationships with category child

In category Model

public function products()
    {
      return $this->hasMany(Products::class, 'category', 'id');
    }

   public function sub() {
          return $this->hasMany(SELF::class, 'parent_id')->withCount(['products'])->with('sub');
   }

I would like to get in each category and child category has products count

Example Category A is the parent Category B and C are child of A category Category B has 3 products and category C 2 products

How to get product_count 5 (total of B and C category product count) in the A parent category when I call withCount()?

At the moment there is 0 count but child categories have product count.

Thank you so much for any idea!

Which is max nesting? I mean, if category B and C can have childrens — V-K
Basically I would get unlimited quantity of childs for every single category. — VitBit

Donkarnash Donkarnash · Accepted Answer · 2020-12-28T15:33:48

One way to get the count of all products for a category including products under it's sub categories can be via two separate queries.

Assume:

Category A has id of 5
Category B and Category C are sub categories of A

//Get the Category record with sub categories
//Select only id column from category
//Select id & parent_id (required as it links the relation) from sub (categories)
$category = Category::with('sub:id,parent_id')
    ->select('id')
    ->findOrFail(5);

//Prepare a collection of just the id's 
$categoryIds = $category->sub->pluck('id')->push($category->id);

//Get the product count
$count = Products::whereIn('category', $categoryIds)->count();

I appreciate that! It's only got products count from 2 next child, not whole nested categories.

Define a method to get all ids from the subcategories

class Category extends Model
{
    public function getSubcategoryIds()
    {
        $ids = $this->sub->pluck('id');
        
        $this->sub->each(function($sub) use(&$ids){            
            if($sub->sub->count()) {            
                $ids->push($sub->getSubcategoryIds());
            }
        });      

        return $ids->flatten();
    }

    //... rest of the class code
}

Then

$category = Category::findOrFail(5);

//Get the subcategory ids deeply nested and add category id to it
$categoryIds = $category->getSubcategoryIds()->push($category->id);


//Get the product count
$count = Products::whereIn('category', $categoryIds)->count();

Laravel total products count from childs in parent category

1 Answers