How to retrieve count of items in top level categories and including items in immediate sub-categories using mysql query

Question

Hi

I tried ..

SELECT  c.* , (
    SELECT COUNT( * )
    FROM item t
    WHERE t.cat_id = c.cat_id
    )ct_items, (

    SELECT COUNT( * )
    FROM item t
    INNER JOIN cat c3 ON t.cat_id = c3.cat_id
    AND c3.cat_id = c.parent_id
    ) ct_sub
FROM cat c
WHERE parent_id = '0'
ORDER BY name

but got Unknown column 'c.parent_id' in 'on clause'. Any ideas why I am getting this or another way to achieve this using mysql query? I can workout the numbers using multiple queries and using php etc though.

Thanks

Answer 1

You don't necessarily have to do everything in one query; sometimes trying to glue queries together ends up with worse performance (especially when correlated subqueries are involved). Two queries is OK; it's when you end up calling a new query for each row you've got problems.

So you could get the category items:

SELECT c0.*, COUNT(i0.id) AS cat_nitems
FROM cat AS c0
LEFT JOIN item AS i0 ON i0.cat_id=c0.cat_id
WHERE c0.parent_id= '0'
GROUP BY c0.cat_id
ORDER BY c0.name

and then separately get the subcategory items using a parent-child self-join:

SELECT c0.*, COUNT(i1.id) AS subcats_nitems
FROM cat AS c0
LEFT JOIN cat AS c1 ON c1.parent_id=c0.cat_id
LEFT JOIN item AS i1 ON item.cat_id=c1.cat_id
WHERE c0.parent_id= '0'
GROUP BY c0.cat_id
ORDER BY c0.name

And yes, you can join them both into a single query:

SELECT c0.*, COUNT(DISTINCT i0.id) AS cat_nitems, COUNT(DISTINCT i1.id) AS subcats_nitems
FROM cat AS c0
LEFT JOIN cat AS c1 ON c1.parent_id=c0.cat_id
LEFT JOIN item AS i0 ON item.cat_id=c0.cat_id
LEFT JOIN item AS i1 ON item.cat_id=c1.cat_id
WHERE c0.parent_id= '0'
GROUP BY c0.cat_id
ORDER BY c0.name

I would suspect the larger join and DISTINCT processing might make it less efficient. But then I guess on a small database you ain't gonna notice.

Either way, this will only work for two-deep nesting. If you need sub-sub-categories or arbitrary-depth trees in general, you should consider a schema that's better at modelling trees, such as nested sets.