A repository of tips and tricks (in both English and French) curated by Mirego’s engineering team.
  • postgresql

Détecter les références circulaire dans une unique table

WITH RECURSIVE circular_subordinates(supervisor_id, subordinate_id, depth, path, cycle) AS (
	SELECT s.supervisor_id, s.subordinate_id, 1,
		ARRAY[s.supervisor_id],
		false
	FROM subordinate s
	UNION ALL
	SELECT s.supervisor_id, s.subordinate_id, cs.depth + 1,
		path || s.supervisor_id,
		s.supervisor_id = ANY(path)
	FROM subordinate s, circular_subordinates cs
	WHERE s.supervisor_id = cs.subordinate_id AND NOT cycle
	)

SELECT
depth,
array_to_string(path, ' > ') circular_subordinates
FROM circular_subordinates
WHERE cycle and path[1] = path[array_upper(path, 1)]
group by 1,2

source: https://mccalljt.io/blog/2017/01/postgres-circular-references/