I’m trying to use the modified preorder tree traversal method as layed out in the excellent sitepoint article to retrieve a category tree with a count of items below each category.
So far I’ve come up with this query which is so very nearly there but only returns rows for categories that contain an item.
SELECT root.name,
root.category_id,
COUNT( items.item_id ) AS item_count
FROM categories AS current, categories AS root, items
WHERE root.category_id != 1
AND ( current.lft BETWEEN root.lft AND root.rgt )
AND ( current.category_id = category_id )
GROUP BY root.category_id
ORDER BY current.lft ASC
SELECT root.name
, root.category_id
, COUNT( items.item_id ) AS item_count
FROM categories AS current
, categories AS root
, items
WHERE root.category_id != 1
AND ( current.lft BETWEEN root.lft AND root.rgt )
AND ( current.category_id = [COLOR="Red"]category_id [/COLOR])
GROUP
BY root.category_id
ORDER
BY current.lft ASC
first, which table does the column marked in red belong to? (i’m guessing item)
second, why isn’t the ORDER BY column in the SELECT clause? i’d be surprised if you actually got the sorting to work correctly
hi r937. Apologies, yes you’re right the column in red should be items.category_id
I edited the query slightly shortening some category names etc for clarity and accidentally erased that.
second, why isn’t the ORDER BY column in the SELECT clause? i’d be surprised if you actually got the sorting to work correctly
Ah I hadn’t realised that and you are right the sorting isn’t in fact working it was just a lucky coincidence they were sorted in the right order probably due to the order the data was inserted.
That query is actually an attempt by somebody else after I presented my initial attempt of this monstrosity to them…
SELECT
ips_classifieds_categories.category_id
, ips_classifieds_categories.name
, COUNT(ips_classifieds_items.item_id)
FROM
millnedev.ips_classifieds_categories
LEFT JOIN millnedev.ips_classifieds_items
ON (ips_classifieds_categories.category_id = ips_classifieds_items.category_id)
LEFT JOIN millnedev.ips_classifieds_categories AS ips_classifieds_categories_1
ON (ips_classifieds_items.category_id = ips_classifieds_categories_1.category_id)
WHERE (ips_classifieds_categories_1.lft >= ips_classifieds_categories.lft
AND ips_classifieds_categories_1.rgt <= ips_classifieds_categories.rgt)
GROUP BY ips_classifieds_categories.category_id
ORDER BY ips_classifieds_categories.lft ASC;
I’ve added the ORDER BY column into the SELECT clause and the sorting now appears to be working.
SELECT root.name
, root.category_id
, root.lft
, COUNT( items.item_id ) AS item_count
FROM millnedev.ips_classifieds_categories AS root
INNER
JOIN millnedev.ips_classifieds_categories AS current
ON current.lft BETWEEN root.lft AND root.rgt
LEFT OUTER
JOIN millnedev.ips_classifieds_items AS items
ON items.category_id = current.category_id
WHERE root.category_id <> 1
GROUP
BY root.category_id
ORDER
BY root.lft ASC
OK I got the level depth working with the following…
I’ve had to enter the roots lft and rgt values manually but will do this programatically.
SELECT root.name, root.category_id, root.lft, COUNT( items.item_id ) AS item_count
FROM ips_classifieds_categories root,ips_classifieds_categories current
LEFT OUTER JOIN ips_classifieds_items items ON ( items.category_id = current.category_id )
WHERE root.parent_id <> 0
AND ( SELECT COUNT(*) FROM ips_classifieds_categories depth WHERE current.lft BETWEEN depth.lft AND depth.rgt AND depth.lft BETWEEN 1 AND 18 ) <= 3
AND current.lft BETWEEN root.lft AND root.rgt
GROUP BY root.category_id
ORDER BY root.lft ASC
But I’ve now lost the item count I can see why r937 avoids nested sets now.
Improved the query slightly so I can now also limit by depth…
SELECT node.name, node.category_id, node.lft, node.rgt, node.parent_id, (COUNT(parent.category_id) - (sub_tree.depth + 1)) AS depth
FROM millnedev.ips_classifieds_categories AS node,
millnedev.ips_classifieds_categories AS parent,
millnedev.ips_classifieds_categories AS sub_parent,
(
SELECT node.category_id, (COUNT(parent.category_id) - 1) AS depth
FROM millnedev.ips_classifieds_categories AS node,
millnedev.ips_classifieds_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = '1'
GROUP BY node.category_id
ORDER BY node.lft
) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.category_id = sub_tree.category_id
GROUP BY node.category_id
HAVING depth BETWEEN 1 AND 2
ORDER BY node.lft;
But now need to work out how to factor back in the join to get the item count also.
