[eluser]jwindhorst[/eluser]
I have been implementing a nested sets (folder hierarchy) navigation for a site and I can't seem to get this one query right. This should be getting only the immediate children of a parent node. But it seems that my "depth" variable is not getting set right. Ideal I would like the final depth to be 1 according to everything I've been reading, but I'm getting -9 for some reason.
Please help, I can no longer see the forest for the trees in the way! Thanks
Code:
SELECT
node.id as nid,
node.user_id,
node.name,
node.node_type,
node.leftval,
node.rightval,
node.asset_id,
(node.rightval - node.leftval) as height,
(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM
directories AS node,
directories AS parent,
directories AS sub_parent,
(
SELECT
node.id as nid,
node.name,
(COUNT(parent.name)-1 ) AS depth
FROM
directories,
directories AS node,
directories AS parent
WHERE
node.leftval
BETWEEN parent.leftval AND parent.rightval
AND
node.leftval= " .$parNode['leftval'] . "
GROUP BY node.name
ORDER BY node.leftval
) AS sub_tree
WHERE
node.user_id=$uid
AND
node.leftval BETWEEN parent.leftval AND parent.rightval
AND
node.rightval=(node.leftval+1)
AND
sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth = 1
ORDER BY node.leftval;
