Displaying Total Number Items Per Parent Category

Hi there,

I have a foreach for products and as usual they all have a parent_category column.
I need to have the total number of products for each category but cannot get those numbers.

One of the examples I have tried so far:

PHP Code:

public function all_products($product_lang){
        $db      = \Config\Database::connect();
        $builder = $db->table('products as p');
        $query = $builder->select('*, p.product_category, count(p.product_category) as cat_total')
                ->where('p.product_lang', $product_lang)
                ->join('categories as c', 'c.category_id = p.product_category')
                ->groupBy('p.product_category')
                ->get();

                return $query->getResult();
                
     } 


Actually I get the numbers but as I group them, my foreach doesn't show every item. If I don't group them only 1 result returns.

Let me see if I understand, you are trying to get how many products there are in each category grouping your products, right?

PHP Code:

public function all_products($product_lang){
        $db      = \Config\Database::connect();
        $builder = $db->table('products as p');
        $query = $builder->select('*, p.product_category, (SELECT COUNT(*) FROM '.$db->prefixTable('products').' 
WHERE product_category=p.product_category) as cat_total', false)
                ->where('p.product_lang', $product_lang)
                ->join('categories as c', 'c.category_id = p.product_category')
                ->groupBy('p.product_category')
                ->get();

                return $query->getResult();
                
     } 


God. It worked like a charm and believe me I had never thought such a long select clause.

Thank you very very much.

Btw, yes, you got me true   I was trying to get these numbers in order to let the user change the order of the products within the number(s) of its category. Otherwise, my foreach was showing 300something as the total number for each product. And it was meaningless.

--
Btw, I don't understand why I cant rate you

Good to see it works, about rate, don't worry, I'm a new member here, that's the reason.