Cargando
Lección 7 de 29· CTEs recursivas y jerarquías
En la tienda TechAndino, Lucía dirige a todo el mundo, pero la tabla employees solo sabe una cosa de cada persona: quién es su jefe directo (manager_id). Nadie tiene guardado "soy el jefe del jefe del jefe de Diego". Y sin embargo, la dirección te pide algo muy común:
«Dame a todo el equipo que cuelga de la dirección, con el nivel jerárquico de cada persona.»
Con un JOIN normal no puedes: no sabes de antemano cuántos niveles hay. Cada JOIN extra recorre un nivel más, y tendrías que ir añadiendo joins a mano hasta quedarte sin empleados. Eso es frágil y feo.
La herramienta correcta es la CTE recursiva: una consulta que se refiere a sí misma y va bajando nivel por nivel hasta que no quedan más filas. En esta lección diseccionamos sus cuatro piezas, la vemos ejecutarse paso a paso sobre el organigrama de TechAndino, y aprendemos a evitar el peligro clásico: la recursión infinita.
Recordatorio de contexto: una CTE (Common Table Expression) es la subconsulta con nombre que escribes con
WITH ... AS (...). Ya la usaste en el módulo anterior; aquí le añadimos la palabra claveRECURSIVEpara que pueda llamarse a sí misma.
Una CTE recursiva construye su resultado en oleadas. Empieza con un conjunto de filas "semilla" y, en cada oleada, usa las filas que acaba de encontrar para buscar las siguientes. Cuando una oleada no encuentra nada nuevo, se detiene.
Antes de escribir la recursión, veamos la semilla por separado. Es simplemente la cúspide del organigrama: la persona sin jefe.
-- La "semilla" de nuestra recursión: quien no tiene jefe.
SELECT employee_id, name, manager_id, 1 AS nivel
FROM employees
WHERE manager_id IS NULL;Una sola fila: Lucía Fernández, a la que asignamos nivel = 1. Esa fila es el término ancla (anchor). Todo lo demás nace de aquí.
Una CTE recursiva siempre tiene la misma estructura. Son cuatro partes y ninguna sobra:
| Pieza | Qué es | En nuestro ejemplo |
|---|---|---|
WITH RECURSIVE nombre AS ( | Declara la CTE y avisa a PostgreSQL de que se referenciará a sí misma | WITH RECURSIVE org AS ( |
| Término ancla | La consulta base, sin recursión. Produce las filas de partida | Empleado con manager_id IS NULL (Lucía) |
UNION ALL | Une las filas del ancla con las que va generando la recursión | (obligatorio entre las dos partes) |
| Término recursivo | Una consulta que referencia la propia CTE (org). Se ejecuta una y otra vez | employees e JOIN org ON e.manager_id = org.employee_id |
La condición de parada no es una quinta pieza aparte: es una consecuencia natural del término recursivo. La recursión para cuando el término recursivo no produce filas nuevas. En el organigrama eso ocurre cuando llegamos a empleados que no tienen a nadie a su cargo.
Este diagrama muestra cómo fluyen las piezas:
El detalle clave está en el término recursivo: JOIN org. Ahí, org no es la tabla employees, sino las filas que la CTE encontró en la oleada anterior. Por eso cada pasada baja exactamente un nivel: enganchamos a cada empleado con el conjunto de "jefes ya descubiertos".
Ahora sí, la consulta completa:
WITH RECURSIVE org AS (
-- 1) Término ancla: la cúspide del organigrama
SELECT employee_id, name, manager_id, 1 AS nivel
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 2) Término recursivo: engancha cada empleado con su jefe ya descubierto
SELECT e.employee_id, e.name, e.manager_id, org.nivel + 1
FROM employees e
JOIN org ON e.manager_id = org.employee_id
)
SELECT nivel, employee_id, name, manager_id
FROM org
ORDER BY nivel, employee_id;Obtienes a los 8 empleados, cada uno con su nivel correcto (1 para Lucía, 2 para sus reportes directos, y así sucesivamente). Fíjate en org.nivel + 1: como cada fila hereda el nivel de su jefe y le suma uno, el nivel se calcula solo, sin que tú sepas cuántos niveles hay.
PostgreSQL evalúa la recursión con un algoritmo muy concreto:
org. Las filas nuevas pasan a ser la siguiente tabla de trabajo y se acumulan en el resultado.Sigue el proceso paso a paso sobre el organigrama real de TechAndino. En cada paso se resaltan las filas que entran en esa oleada:
Observa cómo la condición de parada no la escribimos: emerge sola. La recursión termina en la oleada 4 porque no hay más subordinados que descubrir. Ese es el patrón sano: los datos se agotan y el proceso converge.
¿Y si los datos no se agotan? Imagina que, por un error de captura, alguien pusiera a Lucía como reporte de Diego (manager_id de Lucía = Diego, y Diego sigue reportando hacia arriba). Ahora hay un ciclo: Lucía → ... → Diego → Lucía → ... El término recursivo nunca deja de encontrar "filas nuevas" y la consulta corre para siempre (en tu servidor local se comería CPU y memoria hasta reventar; en este entorno del navegador simplemente se colgaría).
Hay tres defensas prácticas, y conviene tenerlas siempre a mano:
WHERE org.nivel < 10 (o el máximo razonable) al término recursivo. Aunque haya un ciclo, la recursión se corta.UNION en vez de UNION ALL: UNION elimina filas duplicadas exactas, lo que ayuda en algunos casos, pero no protege si cada fila trae un nivel distinto (entonces nunca son idénticas). No confíes solo en esto.Aquí tienes la versión blindada, con guarda de profundidad y camino anti-ciclos:
WITH RECURSIVE org AS (
SELECT employee_id, name, manager_id, 1 AS nivel,
ARRAY[employee_id] AS camino -- ids visitados
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, org.nivel + 1,
org.camino || e.employee_id
FROM employees e
JOIN org ON e.manager_id = org.employee_id
WHERE org.nivel < 10 -- 1) guarda de profundidad
AND e.employee_id <> ALL(org.camino) -- 2) evita ciclos
)
SELECT nivel, name, camino
FROM org
ORDER BY nivel, employee_id;El resultado sobre TechAndino es idéntico al de la versión simple (no hay ciclos en estos datos), pero ahora la consulta es segura: si mañana entra un dato corrupto que forme un bucle, la guarda org.nivel < 10 o el filtro e.employee_id <> ALL(org.camino) la detienen. En producción, esta es la versión que quieres escribir por defecto.
Dato útil: PostgreSQL 14+ ofrece una cláusula
CYCLEnativa que automatiza la detección de ciclos. El patrón manual del array funciona en cualquier versión y deja explícito qué estás protegiendo, por eso lo mostramos así.
WITH RECURSIVE) resuelve jerarquías de profundidad desconocida recorriéndolas nivel a nivel.UNION ALL → término recursivo (referencia la propia CTE) → parada implícita cuando no hay filas nuevas.nivel + 1 del jefe.Hora de escribir una recursión completa desde cero. En vez de partir de la cúspide, arrancarás desde un mando intermedio y bajarás por su rama del organigrama.
Escribe una CTE recursiva llamada equipo que devuelva a Carlos Ramírez (employee_id = 2) y a todo su equipo por debajo (directos e indirectos), con su nivel jerárquico relativo a Carlos: Carlos es el nivel 1, sus reportes directos el 2, y así sucesivamente.
Devuelve dos columnas, name y nivel, ordenadas por nivel y, dentro de cada nivel, por employee_id. Debe salir Carlos (1), luego Jorge y Ana (2) y finalmente Diego (3).
WITH RECURSIVE equipo AS (
-- Término ancla: empieza en Carlos Ramírez (employee_id = 2), nivel 1
-- TODO: completa el SELECT y el WHERE del ancla
UNION ALL
-- Término recursivo: engancha a cada empleado con su jefe ya descubierto
-- TODO: completa el JOIN con la CTE 'equipo'
)
SELECT name, nivel
FROM equipo
ORDER BY nivel, employee_id;Gratis