Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Rediska's avatar

How to count products in parent categories?

I have a product table and a category table. A product belongs to one final category through a many-to-many relationship. A category has a category_id and a parent_id column. The category table also has a column with the number of total_products. I want to write a function to count the number of products in each category.

What I do (step by step):

    public function recount()
    {
        $categories = Category::all();
        $tree = [];
        foreach($categories as $item)
        {
            $tree[$item->category_id]['id'] = $item->id;
            $tree[$item->category_id]['parent_id'] = $item->parent_id;
            $tree[$item->category_id]['count'] = $item->products->count();
        }
        // At this stage, I received an array of all categories with products from the final category.

        // After that, I try to run a recursive function to add the number of nested products to each top parent.
        $this->countProducts($tree);
    }

    public function countProducts($tree)
    {
        foreach ($tree as $id => $item) {
            if ($item['parent_id'] !== null && $item['count'] !== 0) {
                $tree[$item['parent_id']]['count'] += $item['count'];
                $this->countProducts($tree);
            }
        }
        return $tree;
    }

My recursion is running endlessly and incorrectly. I can't understand the logic.

Help me please =))

For clarity: At the first stage I get the following array:

  2 => array:3 [▼
    "id" => 31
    "parent_id" => null
    "count" => 0
  ]
  21 => array:3 [▼
    "id" => 32
    "parent_id" => 2
    "count" => 0
  ]
  211 => array:3 [▼
    "id" => 33
    "parent_id" => 21
    "count" => 0
  ]
  212 => array:3 [▼
    "id" => 34
    "parent_id" => 21
    "count" => 0
  ]
  2111 => array:3 [▼
    "id" => 36
    "parent_id" => 211
    "count" => 140
  ]
0 likes
3 replies
amitsolanki24_'s avatar

you can create hasMany relationship with products.

Create hasMany products relationship inside category model file

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

Get categories with product counts

 $categories = Category::withCount('products')->get();

technofreaks's avatar

Hi Check youtube channel LaravelDaily, video : Count Relations By Column: 3 Ways To Optimize Eloquent Query (Not able to post link here)

1 like

Please or to participate in this conversation.