Database: Sorting hierarchical data (Modified Preorder Tree Traversal): How to retrieve immediate children

Question

i am using MySQL with PHP & Doctrine 2.

my question is assuming i am using Modified Preorder Tree Traversal is there a way i can retrieve only immediate children?

Answer 1

In addition to the lft and rgt values you could also store each child's parent id. One advantage of doing this is that if your lft and rgt values get messed up you can regenerate the tree. It also lets you directly determine the immediate children of a parent node.

Answer 2

As you've discovered, this is not so easy in the MPTT design. You know how to get all the descendants:

SELECT * FROM tree WHERE lft BETWEEN 2 AND 11;

What you need is the set of descendants who have no ancestors that are also descendants of the parent node you're starting at.

SELECT * FROM tree t1 WHERE t1.lft BETWEEN 2 AND 11 
  AND NOT EXISTS (SELECT * FROM tree t2 WHERE t2.lft > 2 AND t2.lft < 11 
                    AND t1.lft > t2.lft AND t1.rgt < t2.rgt);