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
—
2010-01-05T22:43:11Z —
#4

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
—
2010-01-05T22:44:50Z —
#5

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
—
2010-01-05T22:46:08Z —
#6

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

Andrew_Millne
—
2010-01-05T22:47:06Z —
#7

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

r937
—
2010-01-05T22:49:23Z —
#8

Andrew_Millne said:

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
—
2010-01-06T12:11:19Z —
#9

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
—
2010-01-06T12:29:58Z —
#10

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

Andrew_Millne
—
2010-01-06T12:49:07Z —
#11

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
—
2010-01-06T13:00:56Z —
#12

Andrew_Millne said:

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
—
2010-01-06T15:05:54Z —
#13

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
—
2010-01-06T21:55:55Z —
#14

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.