CODEWITHSUNDEEP
mysqlsqltreebinary-treetree-traversal
Andy Lastname
Andy Lastname

Reputation: 86

MySQL modified preorder tree traversal - getting a subtree based on a node

I have a standard mptt tree table as per picture here on sitepont. I can't for the life of me find a good way to get an entire subtree based on a chosen node though.

Example (from that linked page): Imagine that the tree is a menu, if someone chooses "Cherry" - I want to get all the items from the "Fruit" node downwards.

So, I can get the ultimate parent row (excluding root which I don't want) using 

SELECT lft, rgt FROM menu_items
WHERE lft < (SELECT lft FROM menu_items WHERE id = 'Cherry') 
AND rgt > (SELECT rgt FROM menu_items WHERE id = 'Cherry') 
AND lft > 1
ORDER BY lft ASC LIMIT 1

Which I think is pretty lousy SQL with the 3 SELECTs - but it does get me lft & rgt which I can use in further query to get all nodes between lft and rgt - thus getting my subtree that "Cherry" is in.

I used that as a basis for doing it all in one query - which resulted in this abomination:

SELECT * FROM menu_items 
WHERE lft BETWEEN 
    (SELECT lft FROM  menu_items
    WHERE lft < (SELECT lft FROM menu_items WHERE id = 'Cherry') 
    AND rgt > (SELECT rgt FROM menu_items WHERE id = 'Cherry') 
    AND lft > 1
    ORDER BY lft ASC LIMIT 1)
AND 
    (SELECT rgt FROM  menu_items
    WHERE lft < (SELECT lft FROM menu_items WHERE id = 'Cherry') 
    AND rgt > (SELECT rgt FROM menu_items WHERE id = 'Cherry') 
    AND lft > 1
    ORDER BY lft ASC LIMIT 1)
ORDER BY lft ASC;

That seems pretty nasty to me - seven SELECTs !

Can this be done elegantly (preferably in one query, but doesn't matter that much - readability > single query) ?

BTW - lft and rgt are my field names for the MPTT numbering

EDIT ::: After experimemtation I realise my clumsy SQL does NOT do what I want. It only works for nodes > 2 levels deeper than root.

My question is this: Referring to the diagram linked at the top of the post - If a user "chooses" any leaf, or parent node of a subtree I need to get that entire subtree, not including root.

So if a user chooses "Cherry", "Banana", "Red", "Yellow" or "Fruit" I need the entire "Fruit" subtree.

Upvotes: 0

Views: 949

Answers (2)

killer
killer

Reputation: 1

------------------------------------------------------------------
SELECT yourtable.* FROM yourtable ,
(SELECT lft as lft_inner,rgt as rgt_inner FROM yourtable WHERE id=178)  
as menu_innner 
WHERE lft > lft_inner  
AND rgt < rgt_inner
AND lft > 1
ORDER BY lft ASC
-----------------------------------------------------------------------

Upvotes: 0

Minesh
Minesh

Reputation: 2302

Try below Query: 

SELECT menu_items.* FROM menu_items ,
(SELECT lft as lft_inner,rgt as rgt_inner FROM menu_items WHERE id = 'Cherry')  
as menu_innner 
WHERE lft < lft_inner  
AND rgt > rgt_inner
AND lft > 1
ORDER BY lft ASC

Upvotes: 1

Related Questions