Nested Sets headache

[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;

[eluser]pistolPete[/eluser]
I didn't look at your query, but did you that there are some ready libraries for CI?
http://ellislab.com/forums/viewreply/545521/

[eluser]jwindhorst[/eluser]
I did know that, and implemented Thunders, but the thread in the forum seems to be a little stale. I was hoping posting it outside might draw more attention.