Получение согласованных данных дерева меню из таблиц меню в 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        |
 +----+-----------+----------+

Каким должен быть запрос?


mysql hierarchical-data tree ancestor
person acme    schedule 23.09.2009    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Когда я делаю это, я структурирую таблицу ancestors немного по-другому. Вместо level я храню pathlength. Также сохраните строку для каждого элемента меню, указывающую на себя, с длиной пути 0.

+---------+--------------+------------+
| menu_id |  ancestor_id | pathlength |
+---------+--------------+------------+
|       1 |            1 |          0 |
|       2 |            2 |          0 |
|       3 |            3 |          0 |
|       4 |            4 |          0 |
|       5 |            5 |          0 |
|       2 |            1 |          1 |
|       3 |            2 |          2 |
|       3 |            1 |          1 |
|       4 |            3 |          3 |
|       4 |            2 |          2 |
|       4 |            1 |          1 |
|       5 |            1 |          1 |
+---------+--------------+------------+

Эти «рефлексивные» записи позволяют вам присоединять набор активных пунктов меню к замыкающей таблице. Изменение уровня на длину пути позволяет исключить рефлексивные записи из набора предков.

Теперь вы можете запросить все пункты меню, которые являются предками «активных» пунктов меню, включая сами активные пункты меню:

SELECT a2.menu_id, m2.title, GROUP_CONCAT(a2.ancestor_id) AS ancestors
FROM menu m1
JOIN ancestors a1 ON (m1.id = a1.menu_id)
JOIN ancestors a2 ON (a1.ancestor_id = a2.menu_id AND a2.pathlength > 0)
JOIN menu m2 ON (a2.menu_id = m2.id)
WHERE m1.active = 1
GROUP BY a2.menu_id;

Результат:

+---------+-----------+-----------+
| menu_id | title     | ancestors |
+---------+-----------+-----------+
|       2 | News      | 1         | 
|       3 | Foo       | 2,1       | 
|       4 | Bar       | 3,2,1     | 
|       5 | Downloads | 1         | 
+---------+-----------+-----------+
person Bill Karwin    schedule 27.09.2009
comment
Спасибо Билл. Как вы получаете значения в столбце длины пути? Я использую ваши запросы на вставку и обновление из вашей книги SQL Antipatterns. Спасибо! - person Nate Starner; 19.07.2012
comment
Билл, нашел ответ в комментарии на вашем сайте! его можно найти здесь: ссылка Большое спасибо! - person Nate Starner; 19.07.2012