Getting consistent menu tree data from menu tables in MySQL
I have a tree / ancestor / query problem that I cannot solve:
I have a menu data table and a table containing all the ancestors of the menu:
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 |
+---------+--------------+-------+
I easily get all active menu entries with their ancestors:
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 |
+----+-----------+----------+
But how can I get all the ancestors needed for the tree? In my result, I need the Foo and News record, so I get a tree with a sequence. It should look like this:
+----+-----------+----------+
| id | title |ancestors |
+----+-----------+----------+
| 2 | News | 1 |
| 3 | Foo | 2,1 |
| 4 | Bar | 3,2,1 |
| 5 | Downloads | 1 |
+----+-----------+----------+
What should the request look like?
source to share
When I do this, I structure the table a bit ancestors
. Instead, level
I save pathlength
. Also save a line for each menu item to point to itself, with a path length of 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 |
+---------+--------------+------------+
These "reflective" entries allow you to join a set of active menu items to the closing table. Changing the level to the length of the path allows you to exclude reflective records from the set of ancestors.
Now you can query all menu items that are ancestors of "active" menu items, including the active menu items themselves:
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;
Result:
+---------+-----------+-----------+
| menu_id | title | ancestors |
+---------+-----------+-----------+
| 2 | News | 1 |
| 3 | Foo | 2,1 |
| 4 | Bar | 3,2,1 |
| 5 | Downloads | 1 |
+---------+-----------+-----------+
source to share