sql - recursivo - instrucción terminada. se agotó la recursividad máxima(100) antes de finalizar la instrucción.
Estructura del árbol y recursión (1)
Utilizando una base de datos PostgreSQL 8.4.14, tengo una tabla que representa una estructura de árbol como el siguiente ejemplo:
CREATE TABLE unit (
id bigint NOT NULL PRIMARY KEY,
name varchar(64) NOT NULL,
parent_id bigint,
FOREIGN KEY (parent_id) REFERENCES unit (id)
);
INSERT INTO unit VALUES (1, ''parent'', NULL), (2, ''child'', 1)
, (3, ''grandchild A'', 2), (4, ''grandchild B'', 2);
id | name | parent_id
----+--------------+-----------
1 | parent |
2 | child | 1
3 | grandchild A | 2
4 | grandchild B | 2
Quiero crear una Lista de control de acceso para esas unidades, donde cada unidad puede tener su propia ACL o heredarla del ancestro más cercano con una ACL propia.
CREATE TABLE acl (
unit_id bigint NOT NULL PRIMARY KEY,
FOREIGN KEY (unit_id) REFERENCES unit (id)
);
INSERT INTO acl VALUES (1), (4);
unit_id
---------
1
4
Estoy usando una vista para determinar si una unidad está heredando su ACL de un ancestro:
CREATE VIEW inheriting_acl AS
SELECT u.id AS unit_id, COUNT(a.*) = 0 AS inheriting
FROM unit AS u
LEFT JOIN acl AS a ON a.unit_id = u.id
GROUP BY u.id;
unit_id | inheriting
---------+------------
1 | f
2 | t
3 | t
4 | f
Mi pregunta es: ¿cómo puedo obtener la unidad más cercana que NO está heredando la ACL de un antepasado? Mi resultado esperado debe ser similar a la siguiente tabla / vista:
unit_id | acl
---------+------------
1 | 1
2 | 1
3 | 1
4 | 4
Una consulta con un CTE recursivo podría hacer el trabajo. Requiere PostgreSQL 8.4 o posterior:
WITH RECURSIVE next_in_line AS (
SELECT u.id AS unit_id, u.parent_id, a.unit_id AS acl
FROM unit u
LEFT JOIN acl a ON a.unit_id = u.id
UNION ALL
SELECT n.unit_id, u.parent_id, a.unit_id
FROM next_in_line n
JOIN unit u ON u.id = n.parent_id AND n.acl IS NULL
LEFT JOIN acl a ON a.unit_id = u.id
)
SELECT unit_id, acl
FROM next_in_line
WHERE acl IS NOT NULL
ORDER BY unit_id
La condición de salto en el segundo tramo de la UNION
es n.acl IS NULL
. Con eso, la consulta deja de atravesar el árbol tan pronto como se encuentra un acl
.
En el SELECT
final solo devolvemos las filas donde se encontró un acl
. Voilá.
Como un aparte: es un anti-patrón para usar el id
. Genérico, no descriptivo como nombre de columna. Lamentablemente, algunos ORM lo hacen de forma predeterminada. Llámalo unit_id
y no tienes que usar alias en las consultas todo el tiempo.