StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGetting consistent menu tree data from menu tables in MySQL
text
Body
copied!<p>I have a tree / ancestor / query problem I'm not able to solve:</p> <p>I have a table holding menu data and a table containing all the ancestors of the menu:</p> <pre><code>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 | +---------+--------------+-------+ </code></pre> <p>I get all active menu entries with their ancestors easily with:</p> <pre><code> 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 | +----+-----------+----------+ </code></pre> <p>But how can I get all the the for the tree necessary ancestors, too? In my result I'd need the entry Foo and News so that I get a consistant tree. It should look like this:</p> <pre><code> +----+-----------+----------+ | id | title |ancestors | +----+-----------+----------+ | 2 | News | 1 | | 3 | Foo | 2,1 | | 4 | Bar | 3,2,1 | | 5 | Downloads | 1 | +----+-----------+----------+ </code></pre> <p>How has the query to be like?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload