如何从基于树遍历算法的结果集生成树视图? [英] How to generate a tree view from this result set based on Tree Traversal Algorithm?
问题描述
CREATE TABLE`categories`(
pre>
`id` int(11)NOT NULL auto_increment,
`category_id` int(11)默认NULL,
`root_id` int(11)默认NULL,
`name` varchar(100)collate utf8_unicode_ci NOT NULL,
'lft` int(11)NOT NULL,
`rht` int(11)NOT NULL,
PRIMARY KEY(`id`),
KEY`category_id`(`category_id`) ,
KEY`lft`(`lft`,`rht`),
KEY`root_id`(`root_id`)
)
基于以下问题:
将修改过的预定义树遍历模型(嵌套集)转换为< ul>
不同之处在于我在一张桌子上有很多树。每一行都有一个代表其父母的外键,即其顶级父代:category_id和root_id。另外我有基于这个例子的lft和rht字段:
http ://articles.sitepoint.com/article/hierarchical-data-database/2
基于以下行:
INSERT INTO`categories` VALUES(1,NULL,NULL,'Fruits',1,14);
INSERT INTO`categories` VALUES(2,1,1,'Apple',2,3);
INSERT INTO`categories` VALUES(3,1,1,'Orange',4,9);
INSERT INTO`categories` VALUES(4,3,1,'Orange Type 1',5,6);
INSERT INTO`categories` VALUES(5,3,1,'Orange Type 2',7,8);
插入`categories` VALUES(6,1,1,'Pear',10,11);
插入`categories` VALUES(7,1,1,'Banana',12,13);
INSERT INTO`categories` VALUES(8,NULL,NULL,'Eletronics',1,14);
插入`categories` VALUES(9,8,8,'手机',2,3);
INSERT INTO`categories` VALUES(10,8,8,'Computers',4,9);
插入`categories` VALUES(11,10,8,'PC',5,6);
插入`categories` VALUES(12,10,8,'MAC',7,8);
插入`categories` VALUES(13,8,8,'Printers',10,11);
INSERT INTO`categories` VALUES(14,8,8,'Cameras',12,13);
如何构建代表这棵树的加固列表?
使用sql语句:
SELECT c。 *,(COUNT(p.id)-1)AS depth
FROM`categorias` AS p
CROSS JOIN类别AS c
WHERE(
c.lft
BETWEEN p.lft
和p.rht
)
GROUP BY c.id
ORDER BY c.lft;
我得到了这个结果:
< img src =https://i.stack.imgur.com/8PRRk.pngalt =alt text>
正如你所看到的,我需要我也可以生成正确的树。
另外,获取树之后,有没有办法按名称排序每个节点?
解决方案我知道了。
您只需将root_id设置为在这个查询语句中,我可以分隔树,并且只提取我正在处理的树:
SELECT c。 *,count(p.id)AS深度
FROM`categories` c
CROSS JOIN类别p
WHERE(
c.lft
p.lft $ b之间$ b和p.rht
)
和c.root_id = p.root_id
GROUP BY c.id
ORDER BY c.root_id,c.lft
I have this table:
CREATE TABLE `categories` (
`id` int(11) NOT NULL auto_increment,
`category_id` int(11) default NULL,
`root_id` int(11) default NULL,
`name` varchar(100) collate utf8_unicode_ci NOT NULL,
`lft` int(11) NOT NULL,
`rht` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`),
KEY `lft` (`lft`,`rht`),
KEY `root_id` (`root_id`)
)
Based on this question: Getting a modified preorder tree traversal model (nested set) into a <ul>
The difference is that I have many trees in one table. Each row has a foreign key representing its parent and its top parent: category_id and root_id. Also I have the lft and rht fields based on this example: http://articles.sitepoint.com/article/hierarchical-data-database/2
Based on this rows:
INSERT INTO `categories` VALUES(1, NULL, NULL, 'Fruits', 1, 14);
INSERT INTO `categories` VALUES(2, 1, 1, 'Apple', 2, 3);
INSERT INTO `categories` VALUES(3, 1, 1, 'Orange', 4, 9);
INSERT INTO `categories` VALUES(4, 3, 1, 'Orange Type 1', 5, 6);
INSERT INTO `categories` VALUES(5, 3, 1, 'Orange Type 2', 7, 8);
INSERT INTO `categories` VALUES(6, 1, 1, 'Pear', 10, 11);
INSERT INTO `categories` VALUES(7, 1, 1, 'Banana', 12, 13);
INSERT INTO `categories` VALUES(8, NULL, NULL, 'Eletronics', 1, 14);
INSERT INTO `categories` VALUES(9, 8, 8, 'Cell Phones', 2, 3);
INSERT INTO `categories` VALUES(10, 8, 8, 'Computers', 4, 9);
INSERT INTO `categories` VALUES(11, 10, 8, 'PC', 5, 6);
INSERT INTO `categories` VALUES(12, 10, 8, 'MAC', 7, 8);
INSERT INTO `categories` VALUES(13, 8, 8, 'Printers', 10, 11);
INSERT INTO `categories` VALUES(14, 8, 8, 'Cameras', 12, 13);
How can I build an ordened list representing this tree?
With the sql bellow:
SELECT c. * , (COUNT( p.id ) -1) AS depth
FROM `categorias` AS p
CROSS JOIN categories AS c
WHERE (
c.lft
BETWEEN p.lft
AND p.rht
)
GROUP BY c.id
ORDER BY c.lft;
I got this result:
As you can see, I need to order by root_id too, so that I can generate the correct tree.
Also, after get the tree, is there a way to order each node by name?
I got it.
All you need to do is set root_id to the top parents too, so that you can ORDER BY correctly.
With the query bellow I can have separeted trees, and uptade only the tree that I'm working on:
SELECT c . * , count( p.id ) AS depth
FROM `categories` c
CROSS JOIN categories p
WHERE (
c.lft
BETWEEN p.lft
AND p.rht
)
AND c.root_id = p.root_id
GROUP BY c.id
ORDER BY c.root_id, c.lft
这篇关于如何从基于树遍历算法的结果集生成树视图?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!