Displaying Total Number Items Per Parent Category

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();
                
     }