从 MySQL 中的菜单表获取一致的菜单树数据
我有一个树/祖先/查询问题,我无法解决:
我有一个保存菜单数据的表和一个包含菜单所有祖先的表:
table menu table ancestors
+-----+------------+--------+ +---------+--------------+-------+
| id | title | active | | menu_id | ancestor_id | level |
+-----+------------+--------+ +---------+--------------+-------+
| 1 | Home | 0 | | 1 | 0 | 0 |
| 2 | News | 0 | | 2 | 1 | 1 |
| 3 | Foo | 0 | | 3 | 2 | 2 |
| 4 | Bar | 1 | | 3 | 1 | 1 |
| 5 | Downloads | 1 | | 4 | 3 | 3 |
+-----+------------+--------+ | 4 | 2 | 2 |
| 4 | 1 | 1 |
| 5 | 1 | 1 |
+---------+--------------+-------+
我可以轻松地获取所有活动菜单条目及其祖先:
SELECT menu.id, menu.title, GROUP_CONCAT(ancestors.ancestor_id) as ancestors
FROM menu, ancestors
WHERE menu.active = 1
GROUP BY (menu.id);
+----+-----------+----------+
| id | title |ancestors |
+----+-----------+----------+
| 4 | Bar | 3,2,1 |
| 5 | Downloads | 1 |
+----+-----------+----------+
但是如何才能我也得到了树所需的所有祖先吗?在我的结果中,我需要条目 Foo 和 News,以便获得一致的树。它应该看起来像这样:
+----+-----------+----------+
| id | title |ancestors |
+----+-----------+----------+
| 2 | News | 1 |
| 3 | Foo | 2,1 |
| 4 | Bar | 3,2,1 |
| 5 | Downloads | 1 |
+----+-----------+----------+
查询怎么样?
I have a tree / ancestor / query problem I'm not able to solve:
I have a table holding menu data and a table containing all the ancestors of the menu:
table menu table ancestors
+-----+------------+--------+ +---------+--------------+-------+
| id | title | active | | menu_id | ancestor_id | level |
+-----+------------+--------+ +---------+--------------+-------+
| 1 | Home | 0 | | 1 | 0 | 0 |
| 2 | News | 0 | | 2 | 1 | 1 |
| 3 | Foo | 0 | | 3 | 2 | 2 |
| 4 | Bar | 1 | | 3 | 1 | 1 |
| 5 | Downloads | 1 | | 4 | 3 | 3 |
+-----+------------+--------+ | 4 | 2 | 2 |
| 4 | 1 | 1 |
| 5 | 1 | 1 |
+---------+--------------+-------+
I get all active menu entries with their ancestors easily with:
SELECT menu.id, menu.title, GROUP_CONCAT(ancestors.ancestor_id) as ancestors
FROM menu, ancestors
WHERE menu.active = 1
GROUP BY (menu.id);
+----+-----------+----------+
| id | title |ancestors |
+----+-----------+----------+
| 4 | Bar | 3,2,1 |
| 5 | Downloads | 1 |
+----+-----------+----------+
But how can I get all the the for the tree necessary ancestors, too? In my result I'd need the entry Foo and News so that I get a consistant tree. It should look like this:
+----+-----------+----------+
| id | title |ancestors |
+----+-----------+----------+
| 2 | News | 1 |
| 3 | Foo | 2,1 |
| 4 | Bar | 3,2,1 |
| 5 | Downloads | 1 |
+----+-----------+----------+
How has the query to be like?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
当我这样做时,我的
ancestors
表的结构略有不同。我存储的是pathlength
,而不是level
。还为每个菜单项存储一行以指向其自身,路径长度为 0。这些“自反”条目允许您将活动菜单项集连接到闭包表。将级别更改为路径长度允许您从祖先集中排除自反条目。
现在,您可以查询作为“活动”菜单项祖先的所有菜单项,包括活动菜单项本身:
结果:
When I do this, I structure the
ancestors
table slightly differently. Instead oflevel
, I storepathlength
. Also store a row for each menu item to point to itself, with a path length of 0.These "reflexive" entries allow you to join the set of active menu items to the closure table. Changing level to pathlength allows you to exclude the reflexive entries from the set of ancestors.
Now you can query all menu items that are ancestors of "active" menu items, including the active menu items themselves:
Result: