CODEWITHSUNDEEP
phpmysqldatabasedoctrinehierarchical-data
Jiew Meng
Jiew Meng

Reputation: 88337

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 Tree Traversal is there a way i can retrieve only immediate children?

Upvotes: 2

Views: 1855

Answers (3)

jay
jay

Reputation: 11

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)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562871

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

Upvotes: 5

GWW
GWW

Reputation: 44161

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.

Upvotes: 2

Related Questions