As consultas hierárquicas , como essas consultas recursivas são conhecidas, agora são suportadas no MySQL 8 .

Resposta antiga

Alternativamente, você pode encontrar um truque dinâmico (e, portanto, potencialmente perigoso) aqui: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries

Você também pode encontrar uma discussão sobre como armazenar dados hierárquicos com outros modelos além de uma Lista de Adjacência (ou seja, a coluna Pai ) aqui: https://stackoverflow.com/questions/192220/what-is-the-most-efficient- maneira elegante de analisar uma mesa plana em uma árvore/

Boa sorte!

Esse design de tabela é um antipadrão SQL "Árvores ingênuas", conforme descrito por Bill Karwin (a partir do slide 48 em sua apresentação SQL Antipatterns Strike Back ). O problema com esse design especificamente é a dificuldade de obter todos os descendentes (ou pais) de um nó. Como você está usando o MySQL, você não pode usar expressões de tabela comuns (a instrução WITH e seu modificador RECURSIVE) presentes em outros RDBMSes.

O que resta é:

• use uma implementação alternativa de estrutura de dados hierárquica (as respostas a esta pergunta podem ser uma boa referência sobre isso)

• construir consultas de autojunção com um limite de profundidade. Para profundidade = 5 você poderia usar algo nas linhas de:

  SELECT *
  FROM comments AS c1
    JOIN comments AS c2 ON (c2.parent_id = c1.id)
    JOIN comments AS c3 ON (c3.parent_id = c2.id)
    JOIN comments AS c4 ON (c4.parent_id = c3.id)
    JOIN comments AS c5 ON (c5.parent_id = c4.id)
  

• use um RDBMS que suporte WITH RECURSIVE (embora isso provavelmente não seja uma opção para a maioria das pessoas)

O MySQL não suporta consultas recursivas como a que você precisa.

O que eu fiz um tempo atrás foi escrever procedimentos armazenados que fornecem o modelo para fazer isso.

Em vez de reinventar a roda, darei os links para meus posts anteriores sobre isso:

• Oct 24, 2011: Encontre o nível mais alto de um campo hierárquico: com vs sem CTEs
• Dec 10, 2012: MySQL: consulta hierárquica em árvore
• Apr 12, 2013: Consulta recursiva no mysql

Resumindo, os Stored Procedures que fiz fazem travessia de árvore de pré-ordem usando processamento de fila

• GetParentIDByID
• GetAncestry
• GetFamilyTree

Pai para todos os filhos (como o procedimento armazenado GetFamilyTree)

• STEP01) Comece com um parent_idem uma fila
• STEP02) Desenfileirar o próximo parent_idcomo o atual
• STEP03) Enfileirar todos os idvalores que têm o valor atualparent_id
• PASSO04) Imprima ou Colete o Comentário
• STEP05) Se a fila não estiver vazia, vá paraSTEP02
• PASSO06) Pronto!!!

Filho para todos os pais (como o procedimento armazenado GetAncestry)

• STEP01) Comece com um idem uma fila
• STEP02) Desenfileirar o próximo idcomo o atual
• STEP03) Enfileirar o parent_idvalor da correnteid
• PASSO04) Imprima ou Colete o Comentário
• STEP05) Se a fila não estiver vazia, vá paraSTEP02
• PASSO06) Pronto!!!

Por favor, olhe os procedimentos armazenados em meus outros posts para ver a implementação.

De uma chance !!!

SELECT  group_concat(@id :=
        (
        SELECT  id
        FROM    comments
        WHERE   parent_id = @id
        )) AS comment
FROM    (
        SELECT  @id := 1
        ) vars
STRAIGHT_JOIN
        comments
WHERE   @id IS NOT NULL

violino