PostgreSQL: nivelar a hierarquia em uma consulta

O armazenamento de dados em uma hierarquia permite flexibilidade e limpeza ao mesmo tempo. Uma relação pai-filho simples, muito parecida com a forma como os arquivos e diretórios são dispostos em um sistema de arquivos ou armazenamento em nuvem.

Uma hierarquia básica seria parecida com esta, usando populações como nosso tipo de dados (pense em classificações de criaturas):

create table populations (
  id        integer not null,
  parent_id integer null
  name      character varying not null
)

id  parent_id  name
-------------------
1   NULL       Clam
2   1          Giant
3   2          Boring
4   NULL       Moray Eel
5   4          Green
6   4          White-Eyed
7   5          Giant

Isso permite que alguém adicione facilmente criaturas e subclassificações da maneira que quiser, sem ter muitas tabelas com estrutura semelhante ou igual.

Agora se trata de consultar. Você tem que responder a solicitações como:

• Quantos tipos de moluscos existem?
• Mostre-me todos os tipos de criaturas com seu “caminho” completo
• Mostrar todos os registros relacionados que são de qualquer tipo de enguia moray

Você pode facilmente encontrar-se escrevendo código de aplicativo que causa consultas N + 1, construindo grandes listas, hashes aninhados, strings, etc, ao mesmo tempo que diminui o desempenho e diminui a legibilidade do código.

Graças à expressão de tabela comum recursiva no PostgreSQL, podemos resolver a maioria dos problemas em uma única consulta.

Aqui está a consulta completa e, em seguida, vamos dividi-la:

WITH RECURSIVE pops (id, level, name, name_path) AS (
    SELECT  id, 0, name, ARRAY[name]
    FROM    populations
    WHERE   parent_id is null

    UNION ALL

    SELECT  p.id, t0.level + 1, p.name, ARRAY_APPEND(t0.name_path, p.name)
    FROM    populations p
            INNER JOIN pops t0 ON t0.id = p.parent_id
)

SELECT  id, level, name_path[1] AS category, ARRAY_TO_STRING(name_path, ' > ')
FROM    pops

Isso começa com o WITH RECURSIVEque diz ao PostgreSQL que estamos introduzindo um CTE recursivo. popsé o alias que dei a ele, seguido por uma lista de campos que estarão na tabela. Não especificamos tipos; eles são copiados da consulta “raiz”.

A seguir, temos uma SELECTdeclaração simples . É aqui que semeamos nosso CTE recursivo. Escolhemos as linhas da tabela subjacente que representam os dados raiz : dados que não têm pai. Eu também adicionei alguns bits de dados aqui, levelpara indicar a profundidade do aninhamento de um registro e, em seguida, inicializei um ARRAYque podemos construir para gerar caminhos para registros filhos. Esta consulta é executada apenas uma vez .

Em seguida, há uma UNION ALLdeclaração para indicar que vamos iniciar a consulta recursiva. A próxima SELECTinstrução é onde encontramos os registros dos filhos. Você vê aqui que nos associamos a pops, que é o conjunto atual de registros para esse CTE. É aqui que estamos adicionando todos os registros filho. Esta consulta é executada até que nenhum resultado seja retornado .

Algumas coisas que estamos fazendo aqui para tornar as coisas mais fáceis e mais limpas:

1. Usando ARRAY_APPEND, estamos adicionando o nome deste elemento aos nomes dos elementos anteriores. Isso nos permite construir o caminho mais tarde, da maneira que acharmos adequado.
2. Nós aumentamos o levelem 1. Pode não fazer diferença para você, mas há casos em que saber o nível pode facilitar as coisas ou indicar um problema com seus dados dependendo das necessidades de seu negócio

Por fim, consultamos o popsCTE. Usando name_path[1]podemos indicar uma “categoria” do registro. Este é o nome dos nós raiz. Então. ARRAY_TO_STRINGconcatena os elementos da matriz usando o segundo argumento como separador. Exatamente como um joinmétodo que você encontraria em JavaScript ou Ruby. Você poderia passar esse array diretamente para o aplicativo e deixá-lo juntar / concat valores conforme necessário também.

Nossos resultados!

id level category   path
1  0     Clam       Clam
2  1     Clam       Clam > Giant
3  1     Clam       Clam > Boring
4  0     Moray Eel  Moray Eel
5  1     Moray Eel  Moray Eel > Green
6  1     Moray Eel  Moray Eel > White-Eyed
7  2     Moray Eel  Moray Eel > Green > Giant

Essa abordagem tem o benefício de bom desempenho, flexibilidade e, uma vez que você entende os CTEs, é bastante acessível. Sinta-se à vontade para tirar dúvidas nos comentários e ajudarei se puder.