How to get the path to a node in a tree - efficiently (associated with the message "parse a flat table into a tree?")

Question

This question is a continuation of this publication:

What is the most efficient / elegant way to parse a flat table into a tree?

I liked the ClosureMap solution, but I have an additional problem to solve.

How can you easily get the path to a specific node in a tree? For example, if you look at the tree provided:

ID node Name

1 'Node 1'
2 'Node 1.1'
3 'Node 2'
4 'Node 1.1.1'
5 'Node 2.1'
6 'Node 1.2'

The path to 1.1.1 will be:

ID = 1, 2, 4

Without making recursive SQL calls, is there an elegant way to get the path?

Answer 1

SELECT ancestor_id FROM ClosureTable WHERE descendant_id = 4; 

It returns the values ​​1, 2, 4. But they are returned in separate lines, and they do not indicate that they are in the correct order (we cannot assume that the numerical order corresponds to the order of the tree hierarchy).

Sometimes you also keep depth for every path in ClosureTable . But even if not, you can calculate how many ancestors a given node has and use for sorting:

 SELECT ct1.ancestor_id, COUNT(*) AS depth FROM ClosureTable ct1 JOIN ClosureTable ct2 ON (ct1.ancestor_id = ct2.descendant_id) WHERE ct1.descendant_id = 4 GROUP BY ct1.ancestor_id ORDER BY depth; 

Yes, it still returns a result in three lines. If you use MySQL, you have access to GROUP_CONCAT() . Otherwise, it is easy to get three lines and combine their values ​​into application code.

Answer 2

I think I found a better solution, and I really didn’t understand this in the message I talked about :).

There was a good article on the site’s site, which showed how to get a specific node using the Left / Right attributes:

http://www.sitepoint.com/article/hierarchical-data-database/2/