Como criar uma consulta recursiva hierárquica do MySQL

Eu tenho uma tabela MySQL que é a seguinte:

id | name | parent_id 19 | category1 | 0 20 | category2 | 19 21 | category3 | 20 22 | category4 | 21 ...... 

Agora, eu quero ter uma única consulta MySQL à qual eu simplesmente forneça o id [por exemplo, digamos ‘id = 19’] então eu deveria pegar todos os seus ids filhos [ie resultado deveria ter ids ’20, 21,22 ‘]. … Além disso, a hierarquia das crianças não é conhecida, pode variar ….

Além disso, eu já tenho a solução usando o loop for ….. Deixe-me saber como conseguir o mesmo usando uma única consulta do MySQL, se possível.

Para versões do MySql que não suportam Expressões de Tabelas Comuns (até a versão 5.7), você conseguiria isso com a seguinte consulta:

 select id, name, parent_id from (select * from products order by parent_id, id) products_sorted, (select @pv := '19') initialisation where find_in_set(parent_id, @pv) and length(@pv := concat(@pv, ',', id)) 

Aqui está um violino .

O valor especificado em @pv := '19' deve ser definido para o id do pai para o qual você deseja selecionar todos os descendentes de.

Isso funcionará também se um pai tiver vários filhos. No entanto, é necessário que cada registro preencha a condição parent_id < id , caso contrário, os resultados não serão concluídos.

Esta consulta usa syntax específica do MySql: variables ​​são atribuídas e modificadas durante sua execução. Algumas suposições são feitas sobre a ordem de execução:

  • A cláusula from é avaliada primeiro. Então é aí que @pv é inicializado.
  • A cláusula where é avaliada para cada registro na ordem de recuperação from aliases. Portanto, é onde uma condição é colocada para include apenas registros para os quais o pai já foi identificado como estando na tree descendente (todos os descendentes do pai primário são adicionados progressivamente a @pv ).
  • As condições nesta cláusula where são avaliadas em ordem e a avaliação é interrompida quando o resultado total é certo. Portanto, a segunda condição deve estar em segundo lugar, pois adiciona o id à lista pai, e isso só deve acontecer se o id passar a primeira condição. A function length é chamada apenas para garantir que essa condição seja sempre verdadeira, mesmo que a string pv , por algum motivo, forneça um valor falso.

Em suma, pode-se considerar que essas suposições são muito arriscadas - não há garantia documentada para elas e, embora funcione consistentemente, a ordem de avaliação pode, em teoria, mudar quando você usa essa consulta como uma visão ou sub-rotina. consulta em uma consulta maior.

Observe também que, para conjuntos de dados muito grandes, essa solução pode ficar lenta, já que a operação find_in_set não é a maneira mais ideal de localizar um número em uma lista, certamente não em uma lista que atinge um tamanho na mesma ordem de grandeza que o número de registros retornados.

Alternativa 1: WITH RECURSIVE , WITH RECURSIVE CONNECT BY

Mais e mais bancos de dados implementam a syntax SQL: 1999 WITH [RECURSIVE] ISO WITH [RECURSIVE] para consultas recursivas (por exemplo, Postgres 8.4+ , SQL Server 2005+ , DB2 , Oracle 11gR2 + , SQLite 3.8.4+ , Firebird 2.1+ , H2 , HyperSQL 2.1. 0+ , Teradata , MariaDB 10.2.2+ ). E a partir da versão 8.0, também o MySql suporta . Com essa syntax, a consulta é assim:

 with recursive cte (id, name, parent_id) as ( select id, name, parent_id from products where parent_id = 19 union all select p.id, p.name, p.parent_id from products p inner join cte on p.parent_id = cte.id ) select * from cte; 

Alguns bancos de dados possuem uma syntax alternativa e não padrão para pesquisas hierárquicas, como a cláusula CONNECT BY disponível nos bancos de dados Oracle. O DB2 também suporta essa syntax alternativa.

O MySQL versão 5.7 não oferece esse recurso. Quando seu mecanismo de database fornece essa syntax, essa é certamente a melhor opção. Se não, considere também as seguintes alternativas.

Alternativa 2: identificadores de estilo de caminho

As coisas se tornam muito mais fáceis se você atribuir valores de id que contenham as informações hierárquicas: um caminho. Por exemplo, no seu caso, isso poderia ser assim:

 ID | NAME 19 | category1 19/1 | category2 19/1/1 | category3 19/1/1/1 | category4 

Então seu select ficaria assim:

 select id, name from products where id like '19/%' 

Alternativa 3: Auto-uniões repetidas

Se você conhece um limite superior para a profundidade da sua tree de hierarquia, você pode usar um sql padrão como este:

 select p6.parent_id as parent6_id, p5.parent_id as parent5_id, p4.parent_id as parent4_id, p3.parent_id as parent3_id, p2.parent_id as parent2_id, p1.parent_id as parent_id, p1.id as product_id, p1.name from products p1 left join products p2 on p2.id = p1.parent_id left join products p3 on p3.id = p2.parent_id left join products p4 on p4.id = p3.parent_id left join products p5 on p5.id = p4.parent_id left join products p6 on p6.id = p5.parent_id where 19 in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id) order by 1, 2, 3, 4, 5, 6, 7; 

Veja este violino

A condição where especifica qual pai você deseja recuperar os descendentes de. Você pode estender essa consulta com mais níveis, conforme necessário.

Do blog Gerenciando Dados Hierárquicos no MySQL

Estrutura da tabela

 +-------------+----------------------+--------+ | 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 | +-------------+----------------------+--------+ 

Inquerir:

 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'; 

Saída

 +-------------+----------------------+--------------+-------+ | 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 | +-------------+----------------------+--------------+-------+ 

A maioria dos usuários de uma vez ou outra lidou com dados hierárquicos em um database SQL e, sem dúvida, aprendeu que o gerenciamento de dados hierárquicos não é para o qual um database relacional se destina. As tabelas de um database relacional não são hierárquicas (como XML), mas são simplesmente uma lista simples. Os dados hierárquicos têm um relacionamento pai-filho que não é representado naturalmente em uma tabela de database relacional. Consulte Mais informação

Consulte o blog para mais detalhes.

EDITAR:

 select @pv:=category_id as category_id, name, parent from category join (select @pv:=19)tmp where parent=@pv 

Saída:

 category_id name parent 19 category1 0 20 category2 19 21 category3 20 22 category4 21 

Referência: Como fazer a consulta SELECT recursiva no Mysql?

Tente esse:

Definição da tabela:

 DROP TABLE IF EXISTS category; CREATE TABLE category ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), parent_id INT, CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES category (id) ) engine=innodb; 

Linhas experimentais:

 INSERT INTO category VALUES (19, 'category1', NULL), (20, 'category2', 19), (21, 'category3', 20), (22, 'category4', 21), (23, 'categoryA', 19), (24, 'categoryB', 23), (25, 'categoryC', 23), (26, 'categoryD', 24); 

Procedimento recursivo armazenado:

 DROP PROCEDURE IF EXISTS getpath; DELIMITER $$ CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT) BEGIN DECLARE catname VARCHAR(20); DECLARE temppath TEXT; DECLARE tempparent INT; SET max_sp_recursion_depth = 255; SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent; IF tempparent IS NULL THEN SET path = catname; ELSE CALL getpath(tempparent, temppath); SET path = CONCAT(temppath, '/', catname); END IF; END$$ DELIMITER ; 

Função de invólucro para o procedimento armazenado:

 DROP FUNCTION IF EXISTS getpath; DELIMITER $$ CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC BEGIN DECLARE res TEXT; CALL getpath(cat_id, res); RETURN res; END$$ DELIMITER ; 

Selecione o exemplo:

 SELECT id, name, getpath(id) AS path FROM category; 

Saída:

 +----+-----------+-----------------------------------------+ | id | name | path | +----+-----------+-----------------------------------------+ | 19 | category1 | category1 | | 20 | category2 | category1/category2 | | 21 | category3 | category1/category2/category3 | | 22 | category4 | category1/category2/category3/category4 | | 23 | categoryA | category1/categoryA | | 24 | categoryB | category1/categoryA/categoryB | | 25 | categoryC | category1/categoryA/categoryC | | 26 | categoryD | category1/categoryA/categoryB/categoryD | +----+-----------+-----------------------------------------+ 

Filtrando linhas com determinado caminho:

 SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%'; 

Saída:

 +----+-----------+-----------------------------------------+ | id | name | path | +----+-----------+-----------------------------------------+ | 20 | category2 | category1/category2 | | 21 | category3 | category1/category2/category3 | | 22 | category4 | category1/category2/category3/category4 | +----+-----------+-----------------------------------------+ 

A melhor abordagem que eu tenho é

  1. Use a linhagem para armazenar trees \ sort \ trace. Isso é mais do que suficiente e funciona milhares de vezes mais rápido para leitura do que qualquer outra abordagem. Ele também permite permanecer nesse padrão mesmo se o database for alterado (como QUALQUER database permitirá que esse padrão seja usado)
  2. Use a function que determina a linhagem para um ID específico.
  3. Use-o como quiser (em seleções, ou em operações CUD, ou até mesmo por trabalhos).

Abordagem de linhagem descr. pode ser encontrado em qualquer lugar, por exemplo aqui ou aqui . Como de function – isso é o que me ensultou.

No final – tem solução mais ou menos simples, relativamente rápida e SIMPLES.

Corpo da function

 -- -------------------------------------------------------------------------------- -- 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 

E então você apenas

 select get_lineage(the_id) 

Espero que ajude alguém 🙂

Fez o mesmo por outra quitação aqui

Mysql select recursive get all child com nível múltiplo

A consulta será:

 SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM ( SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR ',') FROM table WHERE parent_id IN (@pv)) AS lv FROM table JOIN (SELECT @pv:=1)tmp WHERE parent_id IN (@pv)) a; 

Se você precisar de velocidade de leitura rápida, a melhor opção é usar uma tabela de fechamento. Uma tabela de fechamento contém uma linha para cada par ancestral / descendente. Então, no seu exemplo, a mesa de fechamento seria semelhante

 ancestor | descendant | depth 0 | 0 | 0 0 | 19 | 1 0 | 20 | 2 0 | 21 | 3 0 | 22 | 4 19 | 19 | 0 19 | 20 | 1 19 | 21 | 3 19 | 22 | 4 20 | 20 | 0 20 | 21 | 1 20 | 22 | 2 21 | 21 | 0 21 | 22 | 1 22 | 22 | 0 

Depois de ter essa tabela, as consultas hierárquicas se tornam muito fáceis e rápidas. Para obter todos os descendentes da categoria 20:

 SELECT cat.* FROM categories_closure AS cl INNER JOIN categories AS cat ON cat.id = cl.descendant WHERE cl.ancestor = 20 AND cl.depth > 0 

Claro, há uma grande desvantagem sempre que você usa dados desnormalizados como este. Você precisa manter a tabela de fechamento ao lado da tabela de categorias. A melhor maneira é provavelmente usar gatilhos, mas é um pouco complexo rastrear corretamente inserções / atualizações / exclusões para tabelas de fechamento. Como com qualquer coisa, você precisa olhar para suas necessidades e decidir qual abordagem é melhor para você.

Edit : Veja a pergunta Quais são as opções para armazenar dados hierárquicos em um database relacional? para mais opções. Existem diferentes soluções ideais para diferentes situações.

Você pode fazer isso em outros bancos de dados facilmente com uma consulta recursiva (YMMV no desempenho).

A outra maneira de fazer isso é armazenar dois bits extras de dados, um valor para a esquerda e para a direita. Os valores da esquerda e da direita são derivados de uma passagem de pré-ordem da estrutura de tree que você está representando.

Isso é conhecido como Modified Preorder Tree Traversal e permite executar uma consulta simples para obter todos os valores pai de uma só vez. Ele também atende pelo nome “conjunto nested”.

Consulta simples para listar a primeira recursion da criança:

 select @pv:=id as id, name, parent_id from products join (select @pv:=19)tmp where parent_id=@pv 

Resultado:

 id name parent_id 20 category2 19 21 category3 20 22 category4 21 26 category24 22 

… com a esquerda:

 select @pv:=p1.id as id , p2.name as parent_name , p1.name name , p1.parent_id from products p1 join (select @pv:=19)tmp left join products p2 on p2.id=p1.parent_id -- optional join to get parent name where p1.parent_id=@pv 

A solução de @tincot para listar todas as crianças:

 select id, name, parent_id from (select * from products order by parent_id, id) products_sorted, (select @pv := '19') initialisation where find_in_set(parent_id, @pv) > 0 and @pv := concat(@pv, ',', id) 

Teste on-line com o Sql Fiddle e veja todos os resultados.

http://sqlfiddle.com/#!9/a318e3/4/0

É um pouco complicado, verifique isso se está funcionando para você

 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,%'; 

Link para violino SQL http://www.sqlfiddle.com/#!2/e3cdf/2

Substitua com o nome do campo e da tabela adequadamente.

Apenas use a class php do BlueM / tree para fazer a tree de uma tabela de auto-relação no mysql.

Tree e Tree \ Node são classs PHP para manipular dados estruturados hierarquicamente usando referências de ID pai. Um exemplo típico é uma tabela em um database relacional em que o campo “pai” de cada registro faz referência à chave primária de outro registro. Obviamente, o Tree não pode usar apenas dados originados de um database, mas qualquer coisa: você fornece os dados e o Tree os utiliza, independentemente de onde os dados vieram e como foram processados. consulte Mais informação

Aqui está um exemplo do uso do BlueM / tree:

 < ?php require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection $stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); $records = $stm->fetchAll(PDO::FETCH_ASSOC); $tree = new BlueM\Tree($records); ... 

Eu encontrei mais facilmente para:

1) crie uma function que verificará se um item está em qualquer lugar na hierarquia pai de outro. Algo parecido com isto (não vou escrever a function, faça com WHILE DO):

 is_related(id, parent_id); 

no seu exemplo

 is_related(21, 19) == 1; is_related(20, 19) == 1; is_related(21, 18) == 0; 

2) use uma sub-seleção, algo assim:

 select ... from table t join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id)); 

Algo não mencionado aqui, embora um pouco semelhante à segunda alternativa da resposta aceita, mas diferente e de baixo custo para consulta de grande hierarquia e fácil (insert update delete), seria adicionar uma coluna de caminho persistente para cada item.

alguns gostam:

 id | name | path 19 | category1 | /19 20 | category2 | /19/20 21 | category3 | /19/20/21 22 | category4 | /19/20/21/22 

Exemplo:

 -- get children of category3: SELECT * FROM my_table WHERE path LIKE '/19/20/21%' -- Reparent an item: UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%' 

Otimizar o comprimento do caminho e o caminho ORDER BY path usando a codificação base36, em vez da identificação do caminho numérico real

  // base10 => base36 '1' => '1', '10' => 'A', '100' => '2S', '1000' => 'RS', '10000' => '7PS', '100000' => '255S', '1000000' => 'LFLS', '1000000000' => 'GJDGXS', '1000000000000' => 'CRE66I9S' 

https://en.wikipedia.org/wiki/Base36

Suprimir também o separador de barra ‘/’ usando comprimento fixo e preenchimento para o id codificado

Explicação detalhada de otimização aqui: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

FAÇAM

construção de uma function ou procedimento para dividir o caminho para os antepassados ​​recuperados de um item

Eu fiz uma consulta para você. Isso lhe dará Categoria Recursiva com uma Única Consulta:

 SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL UNION SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL UNION SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL UNION SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL ORDER BY NAME,subName,subsubName,subsubsubName 

Aqui está um violino .