Tengo una tabla de MySQL que es la siguiente:
id | name | parent_id
19 | category1 | 0
20 | category2 | 19
21 | category3 | 20
22 | category4 | 21
......
Ahora, quiero tener una sola consulta de MySQL a la que simplemente el suministro de la identificación [por ejemplo, decir 'id = 19'] entonces yo debería obtener todos sus ids hijo [es decir, el resultado debe tener ids '20,21,22'].... Además, la jerarquía de los hijos no se conoce puede variar....
Además, ya tengo la solución utilizando el bucle for..... Dime cómo lograr lo mismo utilizando una sola consulta MySQL si es posible.
Del blog Gestión de datos jerárquicos en MySQL
Estructura de la tabla
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
Consulta:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
Salida
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
La mayoría de los usuarios han tratado en algún momento con datos jerárquicos en una base de datos SQL y sin duda han aprendido que la gestión de datos jerárquicos no es para lo que está pensada una base de datos relacional. Las tablas de una base de datos relacional no son jerárquicas (como XML), sino que son simplemente una lista plana. Los datos jerárquicos tienen una relación padre-hijo que no se representa de forma natural en una tabla de una base de datos relacional. Leer más
Consulte el blog para obtener más detalles.
EDIT:
select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv
Salida:
category_id name parent
19 category1 0
20 category2 19
21 category3 20
22 category4 21
Referencia: ¿Cómo hacer la consulta SELECT recursiva en Mysql?
El mejor enfoque que se me ha ocurrido es
La descr. del linaje se puede encontrar en cualquier lugar, por ejemplo Aquí o aquí. En cuanto a la función - esto es lo que me inspiró.
Al final - consiguió más o menos simple, relativamente rápido, y la solución SIMPLE.
Función's cuerpo
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
READS SQL DATA
BEGIN
DECLARE v_rec INT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE v_res text DEFAULT '';
DECLARE v_papa int;
DECLARE v_papa_papa int DEFAULT -1;
DECLARE csr CURSOR FOR
select _id,parent_id -- @n:=@n+1 as rownum,T1.*
from
(SELECT @r AS _id,
(SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := the_id, @l := 0,@n:=0) vars,
table m
WHERE @r <> 0
) T1
where T1.parent_id is not null
ORDER BY T1.lvl DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open csr;
read_loop: LOOP
fetch csr into v_papa,v_papa_papa;
SET v_rec = v_rec+1;
IF done THEN
LEAVE read_loop;
END IF;
-- add first
IF v_rec = 1 THEN
SET v_res = v_papa_papa;
END IF;
SET v_res = CONCAT(v_res,'-',v_papa);
END LOOP;
close csr;
return v_res;
END
Y luego sólo
select get_lineage(the_id)
Espero que ayude a alguien :)
Es un poco complicado, comprueba si te funciona
select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c having list like '%19,%';
Enlace SQL fiddle http://www.sqlfiddle.com/#!2/e3cdf/2
Sustitúyalo por el nombre de su campo y de la tabla que corresponda.