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

i am using MySQL with PHP & Doctrine 2.

my question is assuming i am using Modified Preorder Tre开发者_JS百科e Traversal is there a way i can retrieve only immediate children?

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

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.

Got the same requirement, a much simpler way would be

select * from tree t1
where t1.nodeleft > 1
and 1 in (select count(*) from tree where tree.nodeleft < t1.nodeleft and tree.noderight > t1.noderight)