Retrieving and Displaying Categories and their children count

I am using PHP via Codeigniter for this scenario.

It's a basic category listing that has items assigned with to those categories. What I'm having problem with is showing the item count beside the category names on the category listing page.

How I was doing it was a very ineffective way i 开发者_JS百科think, and I would really like to know what would be the common practice for this sort of thing.

So I was getting the category list in my controller, and then in my view, on the loop thru the categories, I call a model method to grab the count of the respective cat_id on every iteration. (horror!)

Should I be:

A) Getting the categories AND it's item count from 1 SQL statement

OR

B) Process the category list in the controller and get the categories' corresponding item counts in the controller

If so, how to ?

---

I would prefer to have one query with everything I need, if your DB design allow to do so.

Assuming you have a Categories table and a Products table you should be able to do something like this:

Select C.Category, Count(P.Id) as ProductsCount 
From Categories C left join Products P on C.Id = P.Category_Id
Group by C.Category    
Order by C.Category