mysql 父子节点 排序,父子级排序的MySQL树

I'm trying to display a tree structure in MYSQL with this reference (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) and I'm seeing that only works with 1 parent in same table.

If you have another table to join with, nothing go well.

Example:

Table Menu:

+----+--------+-------+

| id | name | order |

| 1 | Father | 0 |

| 2 | Father | 1 |

| 3 | Son | 0 |

| 4 | Child | 1 |

| 5 | Granson| 2 |

+----+--------+-------+

Table Relations

+----+---------+-----------+

| id | menu_id | parent_id |

| 1 | 1 | NULL |

| 2 | 2 | NULL |

| 3 | 3 | 1 |

| 4 | 4 | 3 |

| 5 | 5 | 4 |

+----+---------+-----------+

Do the SELECT

SELECT child_menu.*, menu.* FROM menu, relations AS child_menu

WHERE menu.id = child_menu.menu_id

GROUP BY menu_id

I Have this:

+----+--------+-------+

| id | name | order |

| 1 | Father | 0 |

| 2 | Father | 1 |

| 3 | Son | 0 |

| 4 | Child | 1 |

| 5 | Granson| 2 |

+----+--------+-------+

I'm trying to leave their children in order in the same SELECT.

From what I see in the examples, only works if the parent is in the same table.

Can someone help me?

thank you

Edited: EXPECTED OUTPUT:

+----+--------+-------+

| id | name | order |

| 1 | Father | 0 |

| 3 | Son | 0 |

| 4 | Child | 1 |

| 5 | Granson| 2 |

| 2 | Father | 1 |

+----+--------+-------+

I.E. Father

Son

Child

Grandson

解决方案

There are other ways to organize hierarchical data besides the methods shown in Mike Hillyer's blog. I like to use a method I call transitive closure table or closure table for short. In this design, you store every path through the hierarchy, as ancestor/descendant pairs.

create table closure (

ancestor int,

descendant int,

length int,

primary key (ancestor,descendant),

key (descendant,ancestor)

);

insert into closure values

(1,1,0),

(1,3,1),

(1,4,2),

(1,5,3),

(2,2,0),

(3,3,0),

(3,4,1),

(3,5,2),

(4,4,0),

(4,5,1),

(5,5,0);

Note that this set includes even the "paths" of length zero, i.e. a menu item is an "parent" of itself.

Now you can join each menu item m to every its set of ancestors a, by joining to paths where m is the descandant. From there, join back to the menu item o which is in the set of ancestors, and you can access the order.

Use GROUP_CONCAT() to make a string of "breadcrumbs" from the order of each in the chain of ancestors, and this becomes a string you can sort by to get the menu order you want.

SELECT m.*, GROUP_CONCAT(o.`order` ORDER BY a.length DESC) AS breadcrumbs

FROM menu AS m

INNER JOIN closure AS a ON a.descendant = m.id

INNER JOIN menu AS o ON a.ancestor = o.id

GROUP BY m.id

ORDER BY breadcrumbs;

+----+----------+-------+-------------+

| id | name | order | breadcrumbs |

+----+----------+-------+-------------+

| 1 | Father1 | 0 | 0 |

| 3 | Son | 0 | 0,0 |

| 4 | Child | 1 | 0,0,1 |

| 5 | Grandson | 2 | 0,0,1,2 |

| 2 | Father2 | 1 | 1 |

+----+----------+-------+-------------+

Note that the breadcrumbs sort as a string, so if you have some order numbers with 2 or 3 digits, you will get irregular results. Make sure your order numbers all have the same number of digits.

As an alternative, you could simply store the breadcrumbs strings in your original menu table:

ALTER TABLE menu ADD COLUMN breadcrumbs VARCHAR(255);

UPDATE menu SET breadcrumbs = '0,0,1,2' WHERE id = 5;

etc.

Then you can do a simpler query:

SELECT * FROM menu ORDER BY breadcrumbs;

But then it's up to you to manually recalculate all affected breadcrumb strings, if you ever change the order of the menu items.