使用嵌套集构建动态菜单 [英] Build dynamic menu using Nested Sets
问题描述
我正在尝试在我的PHP CMS中建立一个动态菜单；页面/类别使用嵌套集模型进行组织.
I am trying to build a dynamic menu in my PHP CMS; the pages/categories are organized using the nested sets model.
全树:
root
A
B
B1
B1.1
B1.2
B2
B2.1
B2.1
C
C1
C2
C3
D
我想将此结果集转换为无序列表，该列表仅显示树的一部分. 例如: 如果单击B，则只显示列表的以下部分:
I want to convert this result set to an unordererd list, which only displays a part of the tree. For example: If I click on B, I want to show only the following part of the list:
A
B
B1
B2
C
D
下一步，如果我单击B1，我要显示此列表:
Next, if I click on B1 I want the to show this list:
A
B
B1
B1.1
B1.2
B2
C
D
等
我使用以下SQL查询从(mysql)数据库中获取所有节点:
I use the following SQL query to get all nodes from the (mysql) database:
SELECT node.id, node.lft, node.rgt, node.name,
GROUP_CONCAT(parent.name ORDER BY parent.lft SEPARATOR "/" ) AS path,
(COUNT(parent.lft) - 1) AS depth
FROM pages AS node, pages AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND ( parent.hidden = "no" AND node.hidden = "no") AND parent.lft > 1
GROUP BY node.id ORDER BY node.lft我设法创建了没有递归的完整列表(使用depth列)，但是我不能像上面显示的那样过滤菜单. 我想我需要获取每个节点的父级的lft和rgt值，并使用PHP过滤掉元素. 但是，如何在同一查询中获得这些值?
I managed to create the full list without recursion (using the depth column), but I can't filter the menu like I have shown above; I think I need to get the parent's lft and rgt value for each node and filter out the elements using PHP. But how can I get these values in the same query?
关于如何实现这一目标还有其他建议吗?
Are there any other suggestions on how to achieve this?
提前谢谢！
推荐答案
以下查询将使您可以利用SQL的hading子句和MySQL的
The following query will allow you to open any path (or sets of paths) by taking advantage of SQL's having clause and MySQL's group_concat function.
以下是我使用的表定义和示例数据:
The following is the table definition and sample data I used:
drop table nested_set;
CREATE TABLE nested_set (
id INT,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (1,'HEAD',1,28);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (2,'A',2,3);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (3,'B',4,17);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (4,'B1',5,10);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (5,'B1.1',6,7);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (6,'B1.2',8,9);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (7,'B2',11,16);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (8,'B2.1',12,13);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (9,'B2.2',14,15);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (10,'C',18,25);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (11,'C1',19,20);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (12,'C2',21,22);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (13,'C3',23,24);
INSERT INTO nested_set (id, name, lft, rgt) VALUES (14,'D',26,27);
以下查询为您提供整棵树(HEAD除外):
The following query gives you the entire tree (except for the HEAD):
SELECT
node.id
, node.lft
, node.rgt
, node.name
, GROUP_CONCAT(parent.name ORDER BY parent.lft SEPARATOR "/" ) AS path
, (COUNT(parent.lft) - 1) AS depth
FROM nested_set AS node
inner join nested_set AS parent
on node.lft BETWEEN parent.lft AND parent.rgt
where parent.lft > 1
GROUP BY node.id
针对示例数据运行时，输出以下内容:
With an output of the following when run against the sample data:
+------+-----+-----+------+-----------+-------+
| id | lft | rgt | name | path | depth |
+------+-----+-----+------+-----------+-------+
| 2 | 2 | 3 | A | A | 0 |
| 3 | 4 | 17 | B | B | 0 |
| 4 | 5 | 10 | B1 | B/B1 | 1 |
| 5 | 6 | 7 | B1.1 | B/B1/B1.1 | 2 |
| 6 | 8 | 9 | B1.2 | B/B1/B1.2 | 2 |
| 7 | 11 | 16 | B2 | B/B2 | 1 |
| 8 | 12 | 13 | B2.1 | B/B2/B2.1 | 2 |
| 9 | 14 | 15 | B2.2 | B/B2/B2.2 | 2 |
| 10 | 18 | 25 | C | C | 0 |
| 11 | 19 | 20 | C1 | C/C1 | 1 |
| 12 | 21 | 22 | C2 | C/C2 | 1 |
| 13 | 23 | 24 | C3 | C/C3 | 1 |
| 14 | 26 | 27 | D | D | 0 |
+------+-----+-----+------+-----------+-------+
上述查询的以下新增内容将为您提供打开各个部分所需的控件:
The following additions to the above query will give you the control you need to open the various sections:
having
depth = 0
or ('<PATH_TO_OPEN>' = left(path, length('<PATH_TO_OPEN>'))
and depth = length('<PATH_TO_OPEN>') - length(replace('<PATH_TO_OPEN>', '/', '')) + 1)
having子句将过滤器应用于按查询分组的结果. "depth = 0"部分是为了确保我们始终有基本菜单节点(A，B，C和D).下一部分是控制哪些节点处于打开状态的部分.它将节点的路径与您要打开的设置路径('')进行比较，以查看是否匹配，并且还确保仅将级别打开到该路径中.可以根据需要复制和添加带有''逻辑的部分或整个部分，以根据需要打开多个路径.确保''不以斜杠(/)结尾.
The having clause applies filters to the results of the group by query. The "depth = 0" part is to ensure that we always have out base menu nodes (A, B, C, and D). The next part is the part that controls which nodes are open. It compares the path of the nodes with a set path you want to open ('') to see if it matches and it also makes sure that it only opens level into the path. The entire or section with the '' logic can be duplicated and added as needed to open multiple paths as needed. Make sure that '' does not end in a trailing slash (/).
以下是一些输出示例，向您展示如何构造查询以获取所需的输出:
The following are some output examples to show you how you would construct queries to get the outputs you wanted:
=========Open B==========
SELECT
node.id
, node.lft
, node.rgt
, node.name
, GROUP_CONCAT(parent.name ORDER BY parent.lft SEPARATOR "/" ) AS path
, (COUNT(parent.lft) - 1) AS depth
FROM nested_set AS node
inner join nested_set AS parent
on node.lft BETWEEN parent.lft AND parent.rgt
where parent.lft > 1
GROUP BY node.id
having
depth = 0
or ('B' = left(path, length('B'))
and depth = length('B') - length(replace('B', '/', '')) + 1)
+------+-----+-----+------+------+-------+
| id | lft | rgt | name | path | depth |
+------+-----+-----+------+------+-------+
| 2 | 2 | 3 | A | A | 0 |
| 3 | 4 | 17 | B | B | 0 |
| 4 | 5 | 10 | B1 | B/B1 | 1 |
| 7 | 11 | 16 | B2 | B/B2 | 1 |
| 10 | 18 | 25 | C | C | 0 |
| 14 | 26 | 27 | D | D | 0 |
+------+-----+-----+------+------+-------+
=========Open B and B/B1==========
SELECT
node.id
, node.lft
, node.rgt
, node.name
, GROUP_CONCAT(parent.name ORDER BY parent.lft SEPARATOR "/" ) AS path
, (COUNT(parent.lft) - 1) AS depth
FROM nested_set AS node
inner join nested_set AS parent
on node.lft BETWEEN parent.lft AND parent.rgt
where parent.lft > 1
GROUP BY node.id
having
depth = 0
or ('B' = left(path, length('B'))
and depth = length('B') - length(replace('B', '/', '')) + 1)
or ('B/B1' = left(path, length('B/B1'))
and depth = length('B/B1') - length(replace('B/B1', '/', '')) + 1)
+------+-----+-----+------+-----------+-------+
| id | lft | rgt | name | path | depth |
+------+-----+-----+------+-----------+-------+
| 2 | 2 | 3 | A | A | 0 |
| 3 | 4 | 17 | B | B | 0 |
| 4 | 5 | 10 | B1 | B/B1 | 1 |
| 5 | 6 | 7 | B1.1 | B/B1/B1.1 | 2 |
| 6 | 8 | 9 | B1.2 | B/B1/B1.2 | 2 |
| 7 | 11 | 16 | B2 | B/B2 | 1 |
| 10 | 18 | 25 | C | C | 0 |
| 14 | 26 | 27 | D | D | 0 |
+------+-----+-----+------+-----------+-------+
=========Open B and B/B1 and C==========
SELECT
node.id
, node.lft
, node.rgt
, node.name
, GROUP_CONCAT(parent.name ORDER BY parent.lft SEPARATOR "/" ) AS path
, (COUNT(parent.lft) - 1) AS depth
FROM nested_set AS node
inner join nested_set AS parent
on node.lft BETWEEN parent.lft AND parent.rgt
where parent.lft > 1
GROUP BY node.id
having
depth = 0
or ('B' = left(path, length('B'))
and depth = length('B') - length(replace('B', '/', '')) + 1)
or ('B/B1' = left(path, length('B/B1'))
and depth = length('B/B1') - length(replace('B/B1', '/', '')) + 1)
or ('C' = left(path, length('C'))
and depth = length('C') - length(replace('C', '/', '')) + 1)
+------+-----+-----+------+-----------+-------+
| id | lft | rgt | name | path | depth |
+------+-----+-----+------+-----------+-------+
| 2 | 2 | 3 | A | A | 0 |
| 3 | 4 | 17 | B | B | 0 |
| 4 | 5 | 10 | B1 | B/B1 | 1 |
| 5 | 6 | 7 | B1.1 | B/B1/B1.1 | 2 |
| 6 | 8 | 9 | B1.2 | B/B1/B1.2 | 2 |
| 7 | 11 | 16 | B2 | B/B2 | 1 |
| 10 | 18 | 25 | C | C | 0 |
| 11 | 19 | 20 | C1 | C/C1 | 1 |
| 12 | 21 | 22 | C2 | C/C2 | 1 |
| 13 | 23 | 24 | C3 | C/C3 | 1 |
| 14 | 26 | 27 | D | D | 0 |
+------+-----+-----+------+-----------+-------+
就是这样.您只需要为每个需要打开的路径重复复制该部分即可.
That's about it. you just keep duplicating that or section for each path you need to open.
请参见> http://mikehillyer.com/articles/managing-hierarchical -data-in-mysql/，如果您需要有关在MySQL中使用嵌套集的常规信息.
See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ in case you need general info about working with nested sets in MySQL.
如果您有任何疑问，请告诉我.
Let me know if you have any questions.
HTH，
-Dipin
这篇关于使用嵌套集构建动态菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
