Exemplo de CTEs Recursivos para Trabalhar com Hierarquias

Imagina só: você tem uma loja online com milhares de produtos, tudo organizadinho — categorias, subcategorias, sub-subcategorias. No site isso vira um menu dropdown bonitão, mas no banco de dados pode virar uma dor de cabeça. Como pegar toda a árvore "Eletrônicos → Smartphones → Acessórios" com uma query só? Como contar quantos níveis tem cada categoria? JOIN normal não resolve — aqui precisa de recursão!

Montando a estrutura de categorias de produtos com CTE recursivo

Uma das tarefas clássicas em bancos de dados relacionais é lidar com estruturas hierárquicas. Imagina que você tem uma árvore de categorias de produtos: categorias principais, subcategorias, sub-subcategorias e por aí vai. Tipo assim:

Eletrônicos
  └── Smartphones
      └── Acessórios
  └── Notebooks
      └── Gamer
  └── Foto e vídeo

Essa estrutura é fácil de mostrar na interface da loja, mas como guardar isso no banco e depois puxar? É aí que entram os CTEs recursivos!

Tabela inicial de categorias

Primeiro vamos criar a tabela categories, que vai guardar os dados das categorias dos produtos:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Identificador único da categoria
    category_name TEXT NOT NULL,          -- Nome da categoria
    parent_category_id INT                -- Categoria pai (NULL pras principais)
);

Olha um exemplo de dados que a gente vai colocar na tabela:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Eletrônicos', NULL),
    ('Smartphones', 1),
    ('Acessórios', 2),
    ('Notebooks', 1),
    ('Gamer', 4),
    ('Foto e vídeo', 1);

O que tá rolando aqui:

• Eletrônicos — é a categoria principal (não tem pai, parent_category_id = NULL).
• Smartphones tá dentro da categoria Eletrônicos.
• Acessórios é subcategoria de Smartphones.
• O resto segue o mesmo esquema.

A estrutura atual dos dados na tabela categories fica assim:

Montando a árvore de categorias com CTE recursivo

Agora a gente quer pegar toda a hierarquia das categorias mostrando o nível de profundidade. Pra isso, bora usar um CTE recursivo.

WITH RECURSIVE category_tree AS (
    -- Query base: pega todas as categorias raiz (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Primeiro nível
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Query recursiva: acha as subcategorias pra cada categoria
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- Aumenta o nível
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Query final: pega os resultados do CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Resultado:

O que tá rolando aqui?

1. Primeiro a query base (SELECT … FROM categories WHERE parent_category_id IS NULL) pega as categorias principais. Aqui é só Eletrônicos com depth = 1.
2. Depois a query recursiva com INNER JOIN adiciona as subcategorias, aumentando o nível (depth + 1).
3. Esse processo repete até achar todas as subcategorias em todos os níveis.

Dicas úteis

O exemplo básico funciona, mas na vida real geralmente precisa de mais coisa. Tipo, se você quer mostrar breadcrumb no site ou mostrar pro gerente qual categoria tem mais subcategorias. Bora ver umas melhorias práticas na query.

1. Adicionando o caminho completo da categoria

Às vezes é útil mostrar o caminho completo da categoria, tipo: Eletrônicos > Smartphones > Acessórios. Dá pra fazer isso usando agregação de string:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- junta as strings
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Resultado:

Agora cada categoria tem o caminho completo mostrando a hierarquia.

2. Contando o número de subcategorias

E se a gente quiser saber quantas subcategorias tem em cada categoria?

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

Resultado:

A tabela mostra que Eletrônicos tem 3 subcategorias (Smartphones, Notebooks, Foto e vídeo), e Smartphones e Notebooks têm uma cada.

Peculiaridades e erros comuns ao usar CTE recursivo

Recursão infinita: Se os dados tiverem ciclos (tipo uma categoria apontando pra ela mesma), a query pode entrar em loop infinito. Pra evitar isso, limita a profundidade com WHERE depth < N ou usa LIMIT.

Otimização: CTE recursivo pode ser lento com muitos dados. Usa índice em parent_category_id pra acelerar.

Erro de UNION ao invés de UNION ALL: Sempre usa UNION ALL em CTE recursivo, senão o PostgreSQL vai tentar tirar duplicados e vai ficar mais lento.

Esse exemplo mostra como CTE recursivo ajuda a trabalhar com estruturas hierárquicas. Saber puxar hierarquia do banco vai te ajudar em vários projetos reais. Tipo montar menu de site, analisar estrutura de empresa ou trabalhar com grafos. Agora tu já tá pronto pra encarar qualquer desafio!

1

Pesquisa/teste

Introdução a CTE, nível 27, lição 4

Indisponível

Introdução a CTE

Introdução a CTE

Abrir