Encontre o nível mais alto de um campo hierárquico: com vs sem CTEs

Você definitivamente tem que fazer um script isso via MySQL Stored Procedure Language

Aqui está uma função armazenada chamada GetParentIDByIDpara recuperar um ParentID dado um ID para pesquisar

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetParentIDByID` $$
CREATE FUNCTION `junk`.`GetParentIDByID` (GivenID INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE rv INT;

    SELECT IFNULL(parent_id,-1) INTO rv FROM
    (SELECT parent_id FROM pctable WHERE id = GivenID) A;
    RETURN rv;
END $$
DELIMITER ;

Aqui está uma Função Armazenada chamada GetAncestrypara Recuperar uma Lista de ParentIDs a partir da Primeira Geração em toda a hierarquia com um ID para começar:

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

Aqui está algo para gerar dados de amostra:

USE junk
DROP TABLE IF EXISTS pctable;
CREATE TABLE pctable
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO pctable (parent_id) VALUES (0);
INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
SELECT * FROM pctable;

Aqui está o que ele gera:

mysql> USE junk
Database changed
mysql> DROP TABLE IF EXISTS pctable;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE pctable
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     parent_id INT,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO pctable (parent_id) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pctable;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         0 |
|  2 |         1 |
|  3 |         2 |
|  4 |         3 |
|  5 |         3 |
|  6 |         4 |
|  7 |         5 |
|  8 |         6 |
|  9 |         4 |
| 10 |         5 |
| 11 |         6 |
| 12 |         7 |
| 13 |         7 |
| 14 |         8 |
| 15 |         9 |
| 16 |        10 |
| 17 |         5 |
| 18 |         6 |
| 19 |         7 |
| 20 |         8 |
| 21 |         8 |
| 22 |         9 |
| 23 |        10 |
| 24 |        11 |
| 25 |         9 |
| 26 |        10 |
| 27 |        11 |
| 28 |        12 |
| 29 |        12 |
| 30 |        13 |
| 31 |        14 |
| 32 |        15 |
+----+-----------+
32 rows in set (0.00 sec)

Aqui está o que as funções geram para cada valor:

mysql> SELECT id,GetParentIDByID(id),GetAncestry(id) FROM pctable;
+----+---------------------+-----------------+
| id | GetParentIDByID(id) | GetAncestry(id) |
+----+---------------------+-----------------+
|  1 |                   0 |                 |
|  2 |                   1 | 1               |
|  3 |                   2 | 2,1             |
|  4 |                   3 | 3,2,1           |
|  5 |                   3 | 3,2,1           |
|  6 |                   4 | 4,3,2,1         |
|  7 |                   5 | 5,3,2,1         |
|  8 |                   6 | 6,4,3,2,1       |
|  9 |                   4 | 4,3,2,1         |
| 10 |                   5 | 5,3,2,1         |
| 11 |                   6 | 6,4,3,2,1       |
| 12 |                   7 | 7,5,3,2,1       |
| 13 |                   7 | 7,5,3,2,1       |
| 14 |                   8 | 8,6,4,3,2,1     |
| 15 |                   9 | 9,4,3,2,1       |
| 16 |                  10 | 10,5,3,2,1      |
| 17 |                   5 | 5,3,2,1         |
| 18 |                   6 | 6,4,3,2,1       |
| 19 |                   7 | 7,5,3,2,1       |
| 20 |                   8 | 8,6,4,3,2,1     |
| 21 |                   8 | 8,6,4,3,2,1     |
| 22 |                   9 | 9,4,3,2,1       |
| 23 |                  10 | 10,5,3,2,1      |
| 24 |                  11 | 11,6,4,3,2,1    |
| 25 |                   9 | 9,4,3,2,1       |
| 26 |                  10 | 10,5,3,2,1      |
| 27 |                  11 | 11,6,4,3,2,1    |
| 28 |                  12 | 12,7,5,3,2,1    |
| 29 |                  12 | 12,7,5,3,2,1    |
| 30 |                  13 | 13,7,5,3,2,1    |
| 31 |                  14 | 14,8,6,4,3,2,1  |
| 32 |                  15 | 15,9,4,3,2,1    |
+----+---------------------+-----------------+
32 rows in set (0.02 sec)

MORAL DA HISTÓRIA: A recuperação de dados recursiva deve ser roteirizada no MySQL

ATUALIZAÇÃO 24/10/2011 17:17 EDT

Aqui está o inverso de GetAncestry. Eu chamo isso de GetFamilyTree.

Aqui está o algoritmo:

• Colocar o ID fornecido na fila
• Ciclo
  • Dequeue em front_id
  • Recupere todos os ids em queue_children cujo parent_id = front_id
  • Anexar queue_children a retval_list (rv)
  • Enfileirar queue_children
  • Repita até que queue e queue_children estejam simultaneamente vazios

Eu acredito que nas minhas aulas de Estruturas de Dados e Algoritmos na faculdade, isso é chamado de algo como passagem de árvore de pré-ordem/prefixo.

Aqui está o código:

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(id) qc
        FROM pctable WHERE parent_id = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    RETURN rv;

END $$

Aqui está o que cada linha produz

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM pctable;
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id)                                                                    |
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
|  1 |         0 |                   0 |                 | 2,3,4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31 |
|  2 |         1 |                   1 | 1               | 3,4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31   |
|  3 |         2 |                   2 | 2,1             | 4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31     |
|  4 |         3 |                   3 | 3,2,1           | 6,9,8,11,18,15,22,25,14,20,21,24,27,32,31                                            |
|  5 |         3 |                   3 | 3,2,1           | 7,10,17,12,13,19,16,23,26,28,29,30                                                   |
|  6 |         4 |                   4 | 4,3,2,1         | 8,11,18,14,20,21,24,27,31                                                            |
|  7 |         5 |                   5 | 5,3,2,1         | 12,13,19,28,29,30                                                                    |
|  8 |         6 |                   6 | 6,4,3,2,1       | 14,20,21,31                                                                          |
|  9 |         4 |                   4 | 4,3,2,1         | 15,22,25,32                                                                          |
| 10 |         5 |                   5 | 5,3,2,1         | 16,23,26                                                                             |
| 11 |         6 |                   6 | 6,4,3,2,1       | 24,27                                                                                |
| 12 |         7 |                   7 | 7,5,3,2,1       | 28,29                                                                                |
| 13 |         7 |                   7 | 7,5,3,2,1       | 30                                                                                   |
| 14 |         8 |                   8 | 8,6,4,3,2,1     | 31                                                                                   |
| 15 |         9 |                   9 | 9,4,3,2,1       | 32                                                                                   |
| 16 |        10 |                  10 | 10,5,3,2,1      |                                                                                      |
| 17 |         5 |                   5 | 5,3,2,1         |                                                                                      |
| 18 |         6 |                   6 | 6,4,3,2,1       |                                                                                      |
| 19 |         7 |                   7 | 7,5,3,2,1       |                                                                                      |
| 20 |         8 |                   8 | 8,6,4,3,2,1     |                                                                                      |
| 21 |         8 |                   8 | 8,6,4,3,2,1     |                                                                                      |
| 22 |         9 |                   9 | 9,4,3,2,1       |                                                                                      |
| 23 |        10 |                  10 | 10,5,3,2,1      |                                                                                      |
| 24 |        11 |                  11 | 11,6,4,3,2,1    |                                                                                      |
| 25 |         9 |                   9 | 9,4,3,2,1       |                                                                                      |
| 26 |        10 |                  10 | 10,5,3,2,1      |                                                                                      |
| 27 |        11 |                  11 | 11,6,4,3,2,1    |                                                                                      |
| 28 |        12 |                  12 | 12,7,5,3,2,1    |                                                                                      |
| 29 |        12 |                  12 | 12,7,5,3,2,1    |                                                                                      |
| 30 |        13 |                  13 | 13,7,5,3,2,1    |                                                                                      |
| 31 |        14 |                  14 | 14,8,6,4,3,2,1  |                                                                                      |
| 32 |        15 |                  15 | 15,9,4,3,2,1    |                                                                                      |
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
32 rows in set (0.04 sec)

Este algoritmo funciona de forma limpa, desde que não haja caminhos cíclicos. Se existir algum caminho cíclico, você terá que adicionar uma coluna 'visitada' à tabela.

Depois de adicionar a coluna visitada, aqui está o algoritmo que bloqueia os relacionamentos cíclicos:

• Colocar o ID fornecido na fila
• Marcar todos os visitados com 0
• Ciclo
  • Dequeue em front_id
  • Recupere todos os ids em queue_children cujo parent_id = front_id e visitou = 0
  • Marque todos os queue_children recuperados com visitou = 1
  • Anexar queue_children a retval_list (rv)
  • Enfileirar queue_children
  • Repita até que queue e queue_children estejam simultaneamente vazios

ATUALIZAÇÃO 24/10/2011 17:37 EDT

Criei uma nova tabela chamada observações e preenchi seus dados de amostra. Mudei os procedimentos armazenados para usar observações em vez de pctable. Aqui está sua saída:

mysql> CREATE TABLE observations LIKE pctable;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO observations VALUES (1,3), (2,5), (3,0), (4,10),(5,0),(6,1),(7,5),(8,10),(9,10),(10,3);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM observations;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         3 |
|  2 |         5 |
|  3 |         0 |
|  4 |        10 |
|  5 |         0 |
|  6 |         1 |
|  7 |         5 |
|  8 |        10 |
|  9 |        10 |
| 10 |         3 |
+----+-----------+
10 rows in set (0.00 sec)

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM observations;
+----+-----------+---------------------+-----------------+-------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id) |
+----+-----------+---------------------+-----------------+-------------------+
|  1 |         3 |                   3 |                 | 6                 |
|  2 |         5 |                   5 | 5               |                   |
|  3 |         0 |                   0 |                 | 1,10,6,4,8,9      |
|  4 |        10 |                  10 | 10,3            |                   |
|  5 |         0 |                   0 |                 | 2,7               |
|  6 |         1 |                   1 | 1               |                   |
|  7 |         5 |                   5 | 5               |                   |
|  8 |        10 |                  10 | 10,3            |                   |
|  9 |        10 |                  10 | 10,3            |                   |
| 10 |         3 |                   3 | 3               | 4,8,9             |
+----+-----------+---------------------+-----------------+-------------------+
10 rows in set (0.01 sec)

ATUALIZAÇÃO 2011-10-24 18:22 EDT

Alterei o código para GetAncestry. Havia WHILE ch > 1deveria serWHILE ch > 0

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM observations;
+----+-----------+---------------------+-----------------+-------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id) |
+----+-----------+---------------------+-----------------+-------------------+
|  1 |         3 |                   3 | 3               | 6                 |
|  2 |         5 |                   5 | 5               |                   |
|  3 |         0 |                   0 |                 | 1,10,6,4,8,9      |
|  4 |        10 |                  10 | 10,3            |                   |
|  5 |         0 |                   0 |                 | 2,7               |
|  6 |         1 |                   1 | 1,3             |                   |
|  7 |         5 |                   5 | 5               |                   |
|  8 |        10 |                  10 | 10,3            |                   |
|  9 |        10 |                  10 | 10,3            |                   |
| 10 |         3 |                   3 | 3               | 4,8,9             |
+----+-----------+---------------------+-----------------+-------------------+
10 rows in set (0.01 sec)

Tente agora !!!

Obtendo todos os pais de um nó especificado:

WITH RECURSIVE tree AS ( 
   SELECT id, 
          name, 
          parent_id,
          1 as level 
   FROM the_table
   WHERE name = 'foo'

   UNION ALL 

   SELECT p.id,
          p.name,
          p.parent_id, 
          t.level + 1
   FROM the_table p
     JOIN tree t ON t.parent_id = p.id
)
SELECT *
FROM tree

Para obter o nó raiz, você pode, por exemplo ORDER BY level, pegar a primeira linha

Obtendo todos os filhos de um nó especificado:

WITH RECURSIVE tree AS ( 
   SELECT id, 
          name, 
          parent_id,
          1 as level 
   FROM the_table
   WHERE name = 'foo'

   UNION ALL 

   SELECT p.id,
          p.name,
          p.parent_id, 
          t.level + 1
   FROM your_table p
     JOIN tree t ON t.id = p.parent_id
)
SELECT *
FROM tree

(observe a condição trocada para a junção na parte recursiva da instrução)

Que eu saiba, os seguintes DBMS suportam CTEs recursivos:

• FirebirdSQL 2.1 (na verdade, o primeiro DBMS OpenSource a implementá-los)
• PostgreSQL 8.4
• DB2 (não tenho certeza de qual versão exata)
• Oráculo (desde 11.2)
• SQL Server 2005 e posterior
• Teradata
• H2
• Sybase (não sei qual versão exata)

Editar

Com base em seus dados de exemplo, o seguinte recuperaria todas as subárvores da tabela, incluindo o caminho completo para cada nó como uma coluna adicional:

with recursive obs_tree as (
   select id, parent_id, '/'||cast(id as varchar) as tree
   from observations
   where parent_id is null

   union all 

   select t.id, t.parent_id, p.tree||'/'||cast(t.id as varchar)
   from observations t
      join obs_tree p on t.parent_id = p.id
)
select id, parent_id, tree
from obs_tree
order by tree

A saída seria esta:

identificação | parent_id | árvore
----+-----------+---------
  3 | | /3
  1 | 3 | /3/1
  6 | 1 | /3/1/6
 10 | 3 | /3/10
  4 | 10 | /3/10/4
  8 | 10 | /3/10/8
  9 | 10 | /3/10/9
  5 | | /5
  2 | 5 | /5/2
  7 | 5 | /5/7

A função GetFamilyTree na resposta de Rolando não funciona quando o id fornecido é maior que 4 inteiros, porque a função FORMAT MySQL adiciona vírgulas para separadores de milhar. Eu modifiquei a função armazenada GetFamilyTree para trabalhar com grandes ids inteiros como abaixo:

WHILE queue_length > 0 DO
    IF queue_length = 1 THEN
    SET front_id = queue;
        SET queue = '';
    ELSE
    SET front_id = SUBSTR(queue,1,LOCATE(',',queue)-1);
        SET pos = LOCATE(',',queue) + 1;
        SET q = SUBSTR(queue,pos);
        SET queue = q;
    END IF;

front_id definido dentro do loop if else.