Tengo una tabla con una jerarquía padre/hijo que admite múltiples niveles (teóricamente infinitos) de anidamiento:
|------|-------------------|-------------| | id | title | parent_id | |------|-------------------|-------------| | 1 | Dashboard | 0 | | 2 | Content | 0 | | 3 | Modules | 0 | | 17 | User Modules | 3 | | 31 | Categories | 17 | | ... | | | |------|-------------------|-------------|Estoy tratando de crear una consulta que produzca una lista concatenada de los elementos principales de cada elemento hasta el elemento principal más alto del árbol:
|------|----------------------| | id | concatenatedParents | |------|----------------------| | 1 | 0 | | 2 | 0 | | 3 | 0 | | 17 | 3,0 | | 31 | 17,3,0 | | ... | | |------|----------------------|Basado en una serie de otras respuestas aquí, he construido la siguiente consulta MySQL:
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 parentsTableVer violín aquí: http://sqlfiddle.com/#!9/48d276f/902/0
Pero esta consulta solo funciona para una identificación de niño determinada (31 en este ejemplo). No logré expandir esta consulta para toda la tabla, ¿hay alguna forma de restablecer las variables del contador en cada fila siguiente de la tabla?
He visto muchas respuestas que sugieren usar un número fijo de uniones, pero sería mucho más preferible una solución que acepte un número variable de niveles.
En MySQL 8 esto es posible gracias a las consultas recursivas (gracias @GMB), pero dado que todavía estamos ejecutando MySQL 5.7, me interesa si también existe una solución para versiones anteriores.
Si está ejecutando MySQL 8.0, esto se resuelve mejor con una consulta recursiva:
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 ididentificación | todos_los_padres -: | :---------- 1 | 0 2 | 0 3 | 0 17 | 3,0 31 | 17,3,0
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