How do I get the count of all the products in a category and its sub categories (and sub-sub categories)?

Question

The category table looks like somewhat as below:

id -- name -- parent_id
1  -- Men    -- 0
2  -- Women  -- 0
3  -- Shirts -- 1
4  -- Half-sleeve -- 3
5  -- Full-sleeve -- 3

Relationship table:

Product_id -- Category Id
1          -- 2
2          -- 2
3          -- 4 ....

I can retrieve the number of products in any one category and its immediate sub categories with ease with ease. But if there are more than 2 levels things get messy.

So my question is How do I get the number of all of the products in Men and its sub categories. Or Shirts and its subcategories?

Any ideas, Thanks.

UPDATE:

I know there is Nested Set Model but I am not in position to change the structure to that now.

Answer 1

If it is possible I would check out the following (hierarchical data): http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

It is hard to get your head around at first, but it makes tasks like this much easier.

If you can't do this, you will have to do a recursive function, e.g.:

$prods = 0;
function getProdsInCat($cat)
{
    global $prods;

    $prods += mysql_result(mysql_query(SELECT COUNT(`Product_id`) FROM `prod_to_cat` WHERE `Category Id` = '".$cat."'),0);


    $moreCats = mysql_query("SELECT `cat_id` FROM `cats` WHERE `parent_id` = '".$cat."'");
    while($cats = mysql_fetch_assoc($moreCats)
    {
        getProdsInCat($cats['cat_id']);
    }
}

Answer 2

Assuming you can add an extra column to the categories table.

Said column will have the path to the category.

id -- name        -- parent_id    path
1  -- Men         -- 0            0/
2  -- Women       -- 0            0/
3  -- Shirts      -- 1            0/1
4  -- Half-sleeve -- 3            0/1/3
5  -- Full-sleeve -- 3            0/1/3

That way finding all the subcategories becomes one query:

SELECT id as CatId FROM categories WHERE path LIKE '0/1/%';

And to get the count of all the products within a category and its childrens is pretty easy too:

SELECT count(p.id) as Total
FROM products as p 
JOIN categories as c ON p.category_id = c.id
WHERE c.path like '0/1/%';

Pretty efficient query.

This article provides more information: More Trees & Hierarchies in SQL