父子级排序的MySQL树 [英] MySQL Tree ordered by parent and child
问题描述
我正在尝试使用此参考在MYSQL中显示树结构( http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/),而我发现该表只能与同一表中的1个父级一起使用.
如果您要连接另一个表,那么一切都不会顺利进行.
示例:
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:
表格菜单:
+----+--------+-------+
| id | name | order |
| 1 | Father | 0 |
| 2 | Father | 1 |
| 3 | Son | 0 |
| 4 | Child | 1 |
| 5 | Granson| 2 |
+----+--------+-------+
表关系
+----+---------+-----------+
| id | menu_id | parent_id |
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | 1 |
| 4 | 4 | 3 |
| 5 | 5 | 4 |
+----+---------+-----------+
执行SELECT
SELECT child_menu.*, menu.* FROM menu, relations AS child_menu
WHERE menu.id = child_menu.menu_id
GROUP BY menu_id
我有这个:
+----+--------+-------+
| id | name | order |
| 1 | Father | 0 |
| 2 | Father | 1 |
| 3 | Son | 0 |
| 4 | Child | 1 |
| 5 | Granson| 2 |
+----+--------+-------+
我正在尝试按相同的SELECT顺序离开他们的孩子.
从我在示例中看到的内容来看,仅当父项位于同一表中时才有效.
有人能帮我吗?
谢谢
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
已编辑:预期输出:
+----+--------+-------+
| id | name | order |
| 1 | Father | 0 |
| 3 | Son | 0 |
| 4 | Child | 1 |
| 5 | Granson| 2 |
| 2 | Father | 1 |
+----+--------+-------+
I.E. Father
Son
Child
Grandson
推荐答案
除Mike Hillyer博客中显示的方法外,还有其他组织层次结构数据的方法.我喜欢使用一种叫传递式闭合表或简称为闭合表的方法.在此设计中,您将层次结构中的每条路径都存储为祖先/后代对.
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.
现在,您可以通过连接到其中m
为取消扫描的路径将每个菜单项m
与其所有祖先a
结合起来.从那里,回到祖先集中的菜单项o
,您可以访问order
.
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
.
使用GROUP_CONCAT()从祖先链中每个祖先的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 |
+----+----------+-------+-------------+
请注意,面包屑按字符串排序,因此,如果您有一些带有2或3位数字的order
数字,则会得到不规则的结果.确保您的order
数字均具有相同的数字.
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.
这篇关于父子级排序的MySQL树的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!