How to sum the found values to the top level in a loop?

Question

I have categories that do/don't have child categories. There are also products that belong to one or more categories.

I need to count the number of products in all parent categories, including child categories.

I get the following PHP array, where the array key is the category ID, and the array value indicates the ID of the parent, ID in the database and the number of products of the final category:

array:3000 [▼
  1 => array:3 [▼
    "id" => 30
    "parent_id" => null
    "count" => 0
  ]
  2 => array:3 [▶]
  21 => array:3 [▶]
  211 => array:3 [▶]
  212 => array:3 [▶]
  213 => array:3 [▶]
  2111 => array:3 [▼
    "id" => 36
    "parent_id" => 211
    "count" => 0
  ]
  21111 => array:3 [▼
    "id" => 37
    "parent_id" => 2111
    "count" => 2510
  ]
  //...

How can I sum the number of products of all child categories in a parent category?

It is clear that you need to use a loop, but I don’t understand how :c

Answer 1

Hi i have a flow for you,

Get all product categories that doesn't have children

sql:

select pc.* from product_categories as pc
where pc.id != (select parent_product_category_id from product_categories as pc2)

Now lets say you have all product_categories without any children

$productCategoriesWithoutChildren = getProductCategoriesWithoutChildren(); // use sql that i gived to you
foreach ($productCategoriesWithoutChildren as $productCategory) {
    $categoryId = $productCategory->getId();
    $count = 0;

    while(true) {
        $category = getCategory($categoryId);
        
        $count = $count + getCountForSingleCategory($category->getId()); // write it by yourself
        saveCountForCategory($category->getId(), $count); // write it by yourself
        
        $parentCategory = $category->getParent();
        if(!$parentCategory){
            break;
        }
        $categoryId = $parentCategory->getId();
    }
}