CODEWITHSUNDEEP
mysqltreetreeview
Drahcir
Drahcir

Reputation: 11972

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

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`

Upvotes: 2

Views: 1446

Answers (1)

Drahcir
Drahcir

Reputation: 11972

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

Upvotes: 3

Related Questions