i'm using MySQL with PHP &lifier Doctrine 2.
my real question is presuming i'm using Modified Preorder Tree Traversal it is possible to way i'm able to retrieve only immediate children?
Additionally towards the lft and rgt values you might store each child's parent id. One benefit of carrying this out is when your lft and rgt values get screwed up you are able to regenerate the tree. Additionally, it allows you directly determine the immediate kids of a parent or gaurdian node.
As is available discovered, this isn't very easy within the MPTT design. You are aware how to obtain all of the descendants:
SELECT * FROM tree WHERE lft BETWEEN 2 AND 11;
The thing you need may be the group of descendants who've no forefathers which are also descendants from the parent node you are beginning 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);