Сортировка поддерева в иерархической структуре данных таблицы закрытия

Я хотел бы попросить вас помочь мне с проблемой сортировки иерархической структуры данных, хранящейся в таблице закрытия.

Я хотел использовать эту структуру для хранения моего веб-сайта. Все работает нормально, но проблема в том, что я не знаю , как сортировать точное поддерево в пользовательском порядке. В настоящее время дерево сортируется в порядке, в котором элементы были добавлены в базу данных.

Моя структура основана на статье Билла Карвина о таблицах Closure и некоторых других сообщениях.

Вот моя структура базы данных 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

Для экземпляра DEMO с __ROOT_ = 1, который получает запрос:

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 (в соответствии с именем или некоторым пользовательским порядком)?

Я видел несколько решений для панировочных сухарей (как сортировать по сухарям), но я не знаю, как их создавать и изменять.

Ответы

Ответ 1

Этот вопрос часто возникает не только для Closure Table, но и для других методов хранения иерархических данных. Это нелегко в любом из проектов.

Решение, которое я придумал для Closure Table, включает в себя одно дополнительное соединение. Каждый node в дереве соединяется с цепочкой своих предков, например, с запросом типа "хлебные крошки". Затем используйте GROUP_CONCAT(), чтобы свернуть панировочные сухари в строку с разделителями-запятыми, сортируя номера идентификаторов по глубине в дереве. Теперь у вас есть строка, по которой вы можете сортировать.

SELECT c2.*, cc2.ancestor AS `_parent`,
  GROUP_CONCAT(breadcrumb.ancestor ORDER BY breadcrumb.depth DESC) AS breadcrumbs
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)
JOIN category_closure AS breadcrumb ON (cc1.descendant = breadcrumb.descendant)
WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1
GROUP BY cc1.descendant
ORDER BY breadcrumbs;

+----+------------+--------+---------+-------------+
| id | name       | active | _parent | breadcrumbs |
+----+------------+--------+---------+-------------+
|  1 | Cat 1      |      1 |    NULL | 1           |
|  3 | Cat  1.1   |      1 |       1 | 1,3         |
|  4 | Cat  1.1.1 |      1 |       3 | 1,3,4       |
|  7 | Cat 1.1.2  |      1 |       3 | 1,3,7       |
|  6 | Cat 1.2    |      1 |       1 | 1,6         |
+----+------------+--------+---------+-------------+

Предостережения:

  • Значения id должны иметь единую длину, потому что сортировка "1,3" и "1,6" и "1,327" может не дать заказ, который вы намереваетесь. Но сортировка "001,003" и "001,006" и "001,327" будет. Таким образом, вам нужно либо запустить свои значения id на 1000000+, либо использовать ZEROFILL для предка и потомка в таблице category_closure.
  • В этом решении порядок отображения зависит от числового порядка идентификаторов категории. Этот числовой порядок значений id может не представлять порядок, который вы хотите отобразить в дереве. Или вы можете захотеть изменить порядок отображения, независимо от значений числового идентификатора. Или вы можете захотеть, чтобы одни и те же данные категории отображались в более чем одном дереве, каждый с различным порядком отображения.
    Если вам нужна свобода, вам нужно хранить значения порядка сортировки отдельно от идентификатора, и решение становится еще более сложным. Но в большинстве проектов приемлемо использовать короткую вырезку, предоставляя двойной идентификатор категории как порядок отображения дерева.

Ваш комментарий:

Да, вы можете сохранить "порядок сортировки брака" в качестве другого столбца таблицы закрытия, а затем использовать это значение вместо ancestor для создания строки палитры. Но если вы это сделаете, вы получите много избыточности данных. То есть, данный предок хранится в нескольких строках, по одному для каждого пути, спускающегося с него. Таким образом, вы должны сохранить одно и то же значение для порядка сортировки для каждого из этих строк, что создает риск аномалии.

Альтернативой было бы создать другую таблицу с одной строкой на отдельного предка в дереве и присоединиться к этой таблице, чтобы получить порядок брака.

CREATE TABLE category_closure_order (
  ancestor INT PRIMARY KEY,
  sibling_order SMALLINT UNSIGNED NOT NULL DEFAULT 1
);

SELECT c2.*, cc2.ancestor AS `_parent`,
  GROUP_CONCAT(o.sibling_order ORDER BY breadcrumb.depth DESC) AS breadcrumbs
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)
JOIN category_closure AS breadcrumb ON (cc1.descendant = breadcrumb.descendant)
JOIN category_closure_order AS o ON breadcrumb.ancestor = o.ancestor
WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1
GROUP BY cc1.descendant
ORDER BY breadcrumbs;

+----+------------+--------+---------+-------------+
| id | name       | active | _parent | breadcrumbs |
+----+------------+--------+---------+-------------+
|  1 | Cat 1      |      1 |    NULL | 1           |
|  3 | Cat  1.1   |      1 |       1 | 1,1         |
|  4 | Cat  1.1.1 |      1 |       3 | 1,1,1       |
|  7 | Cat 1.1.2  |      1 |       3 | 1,1,2       |
|  6 | Cat 1.2    |      1 |       1 | 1,2         |
+----+------------+--------+---------+-------------+