我想请您帮忙解决一个问题,即如何对存储为闭包表的层次数据结构进行排序。
我想使用这种结构来存储我的网站菜单。一切都运行得很好,但问题是我不知道如何按自定义顺序对确切的子树进行排序。目前,树是按照项添加到数据库中的顺序排序的。
我的结构基于Bill Karwin 的文章以及一些其他帖子。
下面是我的 MySQL 数据库结构和一些 DEMO 数据:
--
-- Table `category`
--
CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_czech_ci NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `category` (`id`, `name`, `active`) VALUES
(1, 'Cat 1', 1),
(2, 'Cat 2', 1),
(3, 'Cat 1.1', 1),
(4, 'Cat 1.1.1', 1),
(5, 'Cat 2.1', 1),
(6, 'Cat 1.2', 1),
(7, 'Cat 1.1.2', 1);
--
-- Table `category_closure`
--
CREATE TABLE IF NOT EXISTS `category_closure` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`ancestor` int(11) DEFAULT NULL,
`descendant` int(11) DEFAULT NULL,
`depth` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_category_closure_ancestor_category_id` (`ancestor`),
KEY `fk_category_closure_descendant_category_id` (`descendant`)
) ENGINE=InnoDB;
INSERT INTO `category_closure` (`id`, `ancestor`, `descendant`, `depth`) VALUES
(1, 1, 1, 0),
(2, 2, 2, 0),
(3, 3, 3, 0),
(4, 1, 3, 1),
(5, 4, 4, 0),
(7, 3, 4, 1),
(8, 1, 4, 2),
(10, 6, 6, 0),
(11, 1, 6, 1),
(12, 7, 7, 0),
(13, 3, 7, 1),
(14, 1, 7, 2),
(16, 5, 5, 0),
(17, 2, 5, 1);
以下是我查询一棵树的SELECT语句:
SELECT c2.*, cc2.ancestor AS `_parent`
FROM category AS c1
JOIN category_closure AS cc1 ON (cc1.ancestor = c1.id)
JOIN category AS c2 ON (cc1.descendant = c2.id)
LEFT OUTER JOIN category_closure AS cc2 ON (cc2.descendant = c2.id AND cc2.depth = 1)
WHERE c1.id = __ROOT__ AND c1.active = 1
ORDER BY cc1.depth
对于__ROOT_ = 1的DEMO实例,该查询会得到:
id name active _parent
1 Cat 1 1 NULL
3 Cat 1.1 1 1
6 Cat 1.2 1 1
4 Cat 1.1.1 1 3
7 Cat 1.1.2 1 3
但是如果我需要根据名称或某个自定义顺序更改 Cat 1.1 和 Cat 1.2 的顺序怎么办?
我看到过一些面包屑导航的解决方案(如何按面包屑排序),但不知道如何生成和更改它们。