SQL Avanzado

¿Cómo recorres un organigrama entero con una sola consulta?

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 clave RECURSIVE para que pueda llamarse a sí misma.

La idea en una frase

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.

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í.

Anatomía: las cuatro piezas

Una CTE recursiva siempre tiene la misma estructura. Son cuatro partes y ninguna sobra:

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:

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.

Cómo se ejecuta, oleada por oleada

PostgreSQL evalúa la recursión con un algoritmo muy concreto:

1. Ejecuta el término ancla una vez y guarda esas filas (la primera tabla de trabajo).
2. Ejecuta el término recursivo usando la tabla de trabajo como si fuera org. Las filas nuevas pasan a ser la siguiente tabla de trabajo y se acumulan en el resultado.
3. Repite el paso 2 hasta que una pasada devuelva cero filas.
4. Devuelve la unión de todo lo acumulado.

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.

El peligro: recursión infinita

¿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:

1. Una guarda de profundidad: añade WHERE org.nivel < 10 (o el máximo razonable) al término recursivo. Aunque haya un ciclo, la recursión se corta.
2. Detección de ciclos con un camino visitado: arrastra un array con los ids ya visitados y filtra los que se repiten. Es la defensa robusta.
3. 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:

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 CYCLE nativa 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í.

Recapitulación

• Una CTE recursiva (WITH RECURSIVE) resuelve jerarquías de profundidad desconocida recorriéndolas nivel a nivel.
• Sus cuatro piezas: término ancla (semilla) → UNION ALL → término recursivo (referencia la propia CTE) → parada implícita cuando no hay filas nuevas.
• El nivel se calcula solo heredando nivel + 1 del jefe.
• Siempre que los datos puedan tener ciclos, blinda la consulta con una guarda de profundidad y/o un camino de ids visitados.

Tu turno

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.