how to retrieve hierarchical data retrieved as a collection

Question

I have implemented the following hierarchical data using MySQL

1
 |----- 2
 |----- 3

4
 |----- 5
 |----- 6
        |----- 7



id     |    path     |    level   |    parent_id   |    content  |
-------------------------------------------------------------------
1             1             1           NULL            xxx
2            1:2            2           1               yyy
3            1:3            2           1               abc
4             4             1           NULL            zzz
5            4:5            2           4               yyy
6            4:6            2           4               abc
7            4:6:7          3           6               abc

Assuming I have only these records, how do I retrieve them in a tree structure and yet within a single collection starting with the last tree?

What I expected from the query or stored procedure is to return me the following in exactly this order

id
-----

4
5
6
7
1
2
3

How do I do the same but starting with the first tree?

id
-----
1
2
3
4
5
6
7

Possibly a duplicate : http://stackoverflow.com/questions/11064913/achieve-hierarchy-parent-child-relationship-in-an-effective-and-easy-way — Sashi Kant, Jul 02 '13 at 08:26

score 0 · Answer 1 · answered Jul 02 '13 at 08:26

0

try including order by desc

 ORDER by path desc,id asc

answered Jul 02 '13 at 08:26

chetan

2,876
1
14
15

score 0 · Answer 2 · answered Jul 02 '13 at 09:14

To cope with parents / children possibly something like this:-

SELECT PathTable.*, SUM(OrderVal) AS OrderCalc
FROM 
(
    SELECT id, 
            POW(100, MaxDepth-i-1) * SUBSTRING_INDEX(SUBSTRING_INDEX(path, ':', (i+1)), ':', -1) AS OrderVal
    FROM PathTable
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Sub1
    CROSS JOIN (SELECT MAX(LENGTH(path) - LENGTH(REPLACE(path, ':', ''))) + 1 AS MaxDepth FROM PathTable) Sub2
    WHERE i <= (LENGTH(path) - LENGTH(REPLACE(path, ':', '')))
) Sub1
INNER JOIN PathTable
ON Sub1.id = PathTable.id
GROUP BY PathTable.id
ORDER BY OrderCalc

This is splitting up the path field and calculating an order value based on 100 to the power of the level of the bit of path, taking into account the max number of bits of path, times that bit of path (so 4:6:7 lands up as 7 + 6 * 100^1 + 4 * 100^2), then ordering by that.

100 chosen just as it is bigger than the largest single value in the path.

how to retrieve hierarchical data retrieved as a collection

2 Answers2