SQL Guru needed for MPTT query

[Andrew Millne]

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

Can anybody help?

Many thanks

[r937]

two questions...

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

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

[Andrew Millne]

first, which table does the column marked in red belong to? (i'm guessing item)

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.

[r937]

here you go...

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

[Andrew Millne]

The problem is obviously with...

AND ( current.category_id = items.item_category_id )

as if there is no item in the category then this can't work but I really can't figure out how to restructure the query.

[Andrew Millne]

Thanks very much I'll give that a try now.

[Andrew Millne]

Brilliant thank you very much! this forum should have a reputation feature.

[r937]

this forum should have a reputation feature.

it's been tried, and it only gets abused

but thanks for the kind words

and to think i haven't got a clue about the nested set model!!

[Andrew Millne]

Is there any way to limit the results to n levels deep?

If there's no way to limit it from the query directly maybe I could add a "level" field when inserting the catgegories parent_id.level + 1?

Would add complication when moving categories around then though.

[r937]

i hope that question wasn't directed at me, because i don't do the nested set model (lft,rgt)

[Andrew Millne]

not necessarily no

Is there any particular reason I should aware of why you don't do the nested set model? Am I going up a blind alley here?

In any case I found this which describes using nested set to get a specific level deep so I should be able to work off that.

[r937]

Is there any particular reason I should aware of why you don't do the nested set model?

just personal preference: the nested set model is ~way~ too complicated for me

[Andrew Millne]

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.

[Andrew Millne]

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.