Modified Pre-order Tree Traversal, Retrieve only n levels deep?

Question

I've just started working with this algorithm, it really is a great method. The only thing I'm stuck on is how could I retrieve only n number of levels deep in the tree, for example...

Tree Traversal

Img src = http://www.sitepoint.com/hierarchical-data-database-2/ (Great Article)

In the diagram above, how could I select all children of Food, but only 2 levels deep into the tree (Would be everything except cherry and banana).

You only need answer with pseudo code, but you can with MySQL if you like.

My current SQL query looks like this:

SELECT `treeItems`.`ID` AS `treeItemsID`, `treeItems`.`parent`, `treeItems`.`type`

FROM 
       `treeItems`,
       (
           SELECT `lft`, `rgt` FROM `treeItems` WHERE `ID` = $parent

       ) AS `parentRow`

WHERE  `treeItems`.`lft` > `parentRow`.`lft` AND `treeItems`.`lft` < `parentRow`.`rgt`

Add an additional column that stores each record's depth in the tree, then you merely need add to your `WHERE` clause `AND treeItems.depth <= 2`. — eggyal, Aug 28 '12 at 11:11
@eggyal : I was hoping I could do it only with the left & right values, adding the additional column would be an extra overhead to maintain. — Drahcir, Aug 28 '12 at 11:13

score 3 · Answer 1 · edited May 23 '17 at 11:48

I found another similar question on SO: Modified preorder tree traversal: Selecting nodes 1 level deep , this helped me to solve it.

So, I changed my SQL to:

SELECT `treeItems`.*, (COUNT(`depthJoin`.`ID`) - 1) AS `depth`
FROM   `treeItems`,

       (
       SELECT `lft`, `rgt` FROM `treeItems` WHERE `ID` = $parent

       ) AS `parentRow`

CROSS JOIN `treeItems` AS `depthJoin`

WHERE (`treeItems`.`lft` BETWEEN `depthJoin`.`lft` AND `depthJoin`.`rgt`)
AND   (`treeItems`.`lft` > `parentRow`.`lft` AND `treeItems`.`lft` < `parentRow`.`rgt`)

GROUP BY `treeItems`.`ID`
HAVING `depth` <= $maxDepth

Modified Pre-order Tree Traversal, Retrieve only n levels deep?

1 Answers1