Como criar uma pesquisa recursiva hierárquica do MySQL

Para as versões MySql que não suportam expressões de tabelas comuns( até à versão 5.7), você conseguiria isto 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))

O valor especificado em @pv := '19' deve ser ajustado para id do Pai que você deseja seleccionar todos os descendentes de.

Isto também funcionará se um dos pais tiver múltiplos filhos. No entanto, é necessário que cada registro cumpra a condição parent_id < id, caso contrário os resultados não estará completo.

Esta consulta usa uma sintaxe específica de MySql: as variáveis são atribuídas e modificadas durante a sua execução. Algumas suposições são feitas sobre a ordem de execução:

• a cláusula from é avaliada em primeiro lugar. Então é aí que @pv é iniciado.
• a Cláusula where é avaliada para cada registo pela ordem de recuperação dos pseudónimos from. Então é aqui que uma condição é colocada para incluir apenas registros para os quais o pai já foi identificado como estando na árvore descendente (todos os descendentes do progenitor primário são progressivamente adicionados a @pv).
• as condições desta Cláusula where são avaliadas por 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 dos pais, e isso só deve acontecer se o id passar a primeira condição. A função length só é chamada para garantir que esta condição é sempre verdadeira, mesmo se a sequência pv por alguma razão produzir um valor falso.

Ao todo, pode-se achar estes pressupostos demasiado arriscados para se confiar -- não há nenhuma garantia documentada para eles, e mesmo que funcione consistentemente, a ordem de avaliação pode, em teoria, ainda mudar quando você usa esta consulta como uma visão ou sub-consulta em uma consulta maior.

Note também que para conjuntos de dados muito grandes esta solução pode ficar lenta, uma vez que a operação find_in_set não é a forma mais ideal de encontrar um número em uma lista, certamente não em uma lista que atinge um tamanho na mesma ordem de magnitude que o número de registros retornados.

Alternativa 1: WITH RECURSIVE, CONNECT BY

Mais e mais bancos de dados de implementar o SQL:1999 ISO padrão WITH [RECURSIVE] sintaxe para consultas recursivas (e.g. 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 de versão 8.0, também MySql suporta. Com essa sintaxe, a consulta se parece com isto:

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;

Algumas bases de dados têm uma sintaxe alternativa e não-padrão para pesquisas hierárquicas, tais como:CONNECT BY cláusula disponível nas bases de Dados Oracle. DB2 Também suporta esta sintaxe alternativa.

Alternativa 2: identificadores do tipo Path

As coisas tornam-se muito mais fáceis se atribuir id valores que contêm a informação hierárquica: um caminho. Por exemplo, no seu caso isto pode parecer-se com isto:

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

Então o teu {[24] } ficaria assim:

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

Alternativa 3: Auto-juntas repetidas

Se conheça um limite superior para a profundidade da sua hierarquia, pode usar um padrão sql 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;

Vê isto violino

A condição where especifica qual dos pais você quer recuperar os descendentes. Você pode estender esta consulta com mais níveis conforme necessário.

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

Query:

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 utilizadores ao mesmo tempo lidaram com dados hierárquicos numa base de dados SQL e sem dúvida aprenderam que a gestão dos dados hierárquicos não é o objectivo de uma base de dados relacional. As tabelas de um banco de dados relacional não são hierárquicas (como XML), mas são simplesmente uma lista plana. Dados hierárquicos tem uma relação pai-filho que não é representada naturalmente em uma tabela de banco de dados relacional. Leia mais

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

Resultado:

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

Referência: Como fazer a consulta de selecção recursiva no Mysql?

Tenta estes:

Definição do Quadro:

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 ;

Seleccione o exemplo:

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

Resultado:

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

Filtrar as linhas com um determinado caminho:

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

Resultado:

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

A melhor abordagem que consegui foi

1. usar a linhagem para guardar\sort\trace trees. 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 que DB irá mudar (como qualquer db permitirá que esse padrão seja usado)
2. usar uma função que determina a linhagem para um ID específico.
3. usa - o como quiseres (em selects, ou em operações CUD, ou mesmo por empregos).

Abordagem da linhagem. pode ser encontrado em qualquer lugar, por exemplo Aqui ou aqui. A partir da função - Isso é o que me inspirava.

Corpo da função

-- --------------------------------------------------------------------------------
-- 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 depois só

select get_lineage(the_id)

Mysql select recursive get all child with multiple level

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ê precisa 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. Assim, no seu exemplo, a tabela de fechamento seria como

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

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

Edit : veja a pergunta quais são as opções para armazenar dados hierárquicos numa base de dados relacional? para mais opções. Existem diferentes soluções ótimas para diferentes situações.

Você pode fazê-lo assim em outras bases de dados facilmente com uma consulta recursiva (MMMV no desempenho).

A outra maneira de o fazer é armazenar dois bits extras de dados, um valor esquerdo e direito. O valor esquerdo e direito são derivados de uma passagem pré-ordem da estrutura da árvore que você está representando.

Isto é conhecido como Árvore de pré-ordem modificada transversal e permite-lhe executar uma consulta simples para obter todos os valores-mãe de uma vez. Também usa o nome "conjunto aninhado".

Pesquisa simples para listar os filhos da primeira recursão:

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 junção à 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)

Testa-o online comSQL Fiddle e vê todos os resultados.

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

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

Ligação de violino SQL http://www.sqlfiddle.com/#! 2 / e3cdf/2

Substitua adequadamente o seu campo e nome da tabela.

Basta usar BlueM/tree PHP para fazer árvore de uma tabela de Auto-Relação em mysql.

Árvore e árvore\nó são classes de PHP para lidar com dados estruturados hierarquicamente usando referências de ID Pai. Um exemplo típico é uma tabela em um banco de dados relacional onde o campo "pai" de cada registro referencia a chave primária de outro registro. É claro que a árvore não pode usar apenas dados provenientes de um banco de dados, mas qualquer coisa: Você fornece os dados, e a árvore usa-os, independentemente de onde os dados vieram e como foram processados. Leia mais

Aqui está um exemplo de usar 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); 
...

1) crie uma função que irá verificar se um item está em qualquer lugar na hierarquia dos pais de outro. Algo assim (eu não vou escrever a função, fazê - lo com Enquanto fazer):

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-selecção, algo do género:

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 aceite, mas diferente e de baixo custo para a consulta de grande hierarquia e itens fáceis (inserir actualização apagar), seria adicionar uma coluna de localização persistente para cada item.

Alguns como:

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/%'

Optimizar o tamanho da localização e ORDER BY path usando a codificação base36 em vez do ID real da localização numérica

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

Suprimindo também o separador slash ' / ' usando comprimento fixo e estofamento para o id codificado

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

TODO

Construir uma função ou procedimento para dividir o caminho para os antepassados que recuaram de um item

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