5

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Stephen B
  • 141
  • 2
  • 13
  • Does the solution necessarily have to be an sql query, or a query and some processing after that would be fine too? – Aashish gaba Jul 28 '20 at 09:23
  • post-processing is certainly acceptable, but I want to avoid multiple queries. – Stephen B Jul 28 '20 at 09:31
  • Okay, The question is somewhat similar to this. If it's all about traversal in both ways, from root to leaf and leaf to root, I had written a solution for it. https://stackoverflow.com/a/63120595/13257968 It's written in PHP though. But I think the logic can be used here too. – Aashish gaba Jul 28 '20 at 09:40
  • If there exists some additional relation which allows to process the node strictly after its parent node (for example, when `id < parent_id` for all rows) then the task may be solved using user-defined variables theoretically. If not then only stored procedure may help - and I recommend use this method in any case. – Akina Jul 28 '20 at 09:47

2 Answers2

8

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

Demo on DB Fiddle:

id | all_parents
-: | :----------
 1 | 0          
 2 | 0          
 3 | 0          
17 | 3,0        
31 | 17,3,0
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Thank you, a great solution! But since we would need to upgrade to use this, do you know if a MySQL 5.7 alternative is possible as well? I'll update the question with my version. – Stephen B Jul 28 '20 at 09:34
  • I have upgraded the version but still, it does not support the recursion .... !!! – Engineer S. Saad Feb 08 '21 at 11:04
  • Where does lvl come from - when I try to adapt this to my situation it just says unknown column lvl in field list. – Dan Feb 01 '22 at 17:33
  • 1
    @Dan: lvl comes from the non-recursive statement of the CTE. It could also be written as "SELECT 1 AS lvl", ie. you're selecting a constant and giving it an alias (column name). Then in the recursive statement, you're referring back to it and incrementing it on each level. – user323094 Aug 03 '22 at 14:15
2
CREATE PROCEDURE make_csv_parent ()
BEGIN
    CREATE TABLE temp ( id INT PRIMARY KEY, parent_id INT, parents TEXT);
    INSERT INTO temp (id, parent_id, parents)
    SELECT id, parent_id, parent_id
    FROM menu
    WHERE parent_id = 0;
    WHILE ROW_COUNT() DO
        INSERT IGNORE INTO temp (id, parent_id, parents)
        SELECT menu.id, menu.parent_id, CONCAT(menu.parent_id, ',', temp.parents)
        FROM menu 
        JOIN temp ON menu.parent_id = temp.id;
    END WHILE;
    SELECT id, parents FROM temp;
    DROP TABLE temp;
END

fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25