Ejemplo de CTE recursivos para trabajar con jerarquías

Imagínate: tienes una tienda online con miles de productos, todos bien organizados en estanterías — categorías, subcategorías, sub-subcategorías. En la web se ve como un menú desplegable bonito, pero en la base de datos puede ser un dolor de cabeza. ¿Cómo sacar toda la rama "Electrónica → Smartphones → Accesorios" con una sola consulta? ¿Cómo contar cuántos niveles de anidamiento tiene cada categoría? Los JOIN normales aquí no sirven — ¡necesitas recursividad!

Construyendo la estructura de categorías de productos con CTE recursivos

Uno de los problemas clásicos en bases de datos relacionales es trabajar con estructuras jerárquicas. Imagina que tienes un árbol de categorías de productos: categorías principales, subcategorías, sub-subcategorías y así sucesivamente. Por ejemplo:

Electrónica
  └── Smartphones
      └── Accesorios
  └── Portátiles
      └── Gaming
  └── Foto y vídeo

Esta estructura es fácil de mostrar en las interfaces de las tiendas online, pero ¿cómo la guardas en la base de datos y la consultas? ¡Aquí es donde los CTE recursivos te salvan!

Tabla inicial de categorías

Primero creamos la tabla categories, que va a guardar los datos de las categorías de productos:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Identificador único de la categoría
    category_name TEXT NOT NULL,          -- Nombre de la categoría
    parent_category_id INT                -- Categoría padre (NULL para las principales)
);

Aquí tienes un ejemplo de los datos que vamos a meter en la tabla:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Electrónica', NULL),
    ('Smartphones', 1),
    ('Accesorios', 2),
    ('Portátiles', 1),
    ('Gaming', 4),
    ('Foto y vídeo', 1);

¿Qué está pasando aquí?

• Electrónica — es la categoría principal (no tiene padre, parent_category_id = NULL).
• Smartphones está dentro de la categoría Electrónica.
• Accesorios pertenece a la categoría Smartphones.
• El resto de categorías igual.

La estructura actual de los datos en la tabla categories se ve así:

Construyendo el árbol de categorías con un CTE recursivo

Ahora queremos sacar toda la jerarquía de categorías indicando el nivel de anidamiento. Para eso usamos un CTE recursivo.

WITH RECURSIVE category_tree AS (
    -- Consulta base: selecciona todas las categorías raíz (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Primer nivel de anidamiento
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Consulta recursiva: busca subcategorías para cada categoría
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- Aumenta el nivel de anidamiento
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Consulta final: saca los resultados del CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Resultado:

¿Qué está pasando aquí?

1. Primero la consulta base (SELECT … FROM categories WHERE parent_category_id IS NULL) selecciona las categorías principales. En este caso solo Electrónica con depth = 1.
2. Luego la consulta recursiva con INNER JOIN añade las subcategorías, aumentando el nivel de anidamiento (depth + 1).
3. Este proceso se repite hasta encontrar todas las subcategorías de todos los niveles.

Mejoras útiles

El ejemplo básico funciona, pero en proyectos reales muchas veces necesitas más. Por ejemplo, si quieres hacer breadcrumbs para la web o mostrarle al manager en qué categoría hay más subcategorías. Vamos a ver algunas mejoras prácticas de nuestra consulta.

1. Añadir la ruta completa de la categoría

A veces mola mostrar la ruta completa de la categoría, por ejemplo: Electrónica > Smartphones > Accesorios. Esto se puede hacer usando agregación de strings:

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, -- concatenamos 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:

Ahora cada categoría tiene su ruta completa mostrando la anidación.

2. Contar el número de subcategorías

¿Y si quieres saber cuántas subcategorías tiene cada categoría?

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:

La tabla muestra que Electrónica tiene 3 subcategorías (Smartphones, Portátiles, Foto y vídeo), y Smartphones y Portátiles tienen una cada una.

Detalles y errores típicos al trabajar con CTE recursivos

Recursividad infinita: Si los datos tienen ciclos (por ejemplo, una categoría apunta a sí misma), la consulta puede entrar en bucle infinito. Para evitarlo, puedes limitar la profundidad de la recursión con WHERE depth < N o con límites.

Optimización: Los CTE recursivos pueden ser lentos con muchos datos. Pon índices en parent_category_id para que vaya más rápido.

Error de UNION en vez de UNION ALL: Usa siempre UNION ALL en CTE recursivos, si no PostgreSQL intentará quitar duplicados y la consulta irá mucho más lenta.

Este ejemplo te muestra cómo los CTE recursivos ayudan a trabajar con estructuras jerárquicas. Saber sacar jerarquías de la base de datos te va a servir en un montón de proyectos reales. Por ejemplo, para construir menús de webs, analizar estructuras organizativas o trabajar con grafos. Ahora ya puedes enfrentarte a cualquier reto.

1

Cuestionario/control

Introducción a CTE, nivel 27, lección 4

No disponible

Introducción a CTE

Introducción a CTE

Abrir