I have a table with a parent/child hierarchy that supports multiple (theoretically infinite) levels of nesting:
|------|-------------------|-------------| | id | title | parent_id | |------|-------------------|-------------| | 1 | Dashboard | 0 | | 2 | Content | 0 | | 3 | Modules | 0 | | 17 | User Modules | 3 | | 31 | Categories | 17 | | ... | | | |------|-------------------|-------------|
I am trying to build a query that produces a concatenated list of every item’s parent items up until the highest parent in the tree:
|------|----------------------| | id | concatenatedParents | |------|----------------------| | 1 | 0 | | 2 | 0 | | 3 | 0 | | 17 | 3,0 | | 31 | 17,3,0 | | ... | | |------|----------------------|
Based on a number of other answers here I have constructed the following MySQL query:
SELECT parentsTable._id, GROUP_CONCAT(parentsTable.parent_id SEPARATOR ',') as concatenatedParents FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 31, @l := 0) vars, menu m WHERE @r <> 0 ) as parentsTable
See Fiddle here: http://sqlfiddle.com/#!9/48d276f/902/0
But this query only works for one given child id (31 in this example). I did not succeed to expand this query for the whole table, is there some way to reset the counter variables at every next row in the table?
I have seen many answers that suggest using a fixed number of joins, but a solution that accepts a variable number of levels would be much more preferable.
In MySQL 8 this is possible thanks to recursive queries (thank you @GMB), but since we are still running on MySQL 5.7 I am interested if a solution exists for older versions as well.
Advertisement
Answer
If you are running MySQL 8.0, this is best solved with a recursive query:
with recursive cte as ( select id, parent_id, 1 lvl from mytable union all select c.id, t.parent_id, lvl + 1 from cte c inner join mytable t on t.id = c.parent_id ) select id, group_concat(parent_id order by lvl) all_parents from cte group by id
id | all_parents -: | :---------- 1 | 0 2 | 0 3 | 0 17 | 3,0 31 | 17,3,0