CTEs متكررة للتدرجات الهرمية

لماذا الاستدعاء التكراري؟

البنية

WITH RECURSIVE name AS (
  -- 1. Anchor query: seed rows
  SELECT ...
  UNION ALL
  -- 2. Recursive step: references the CTE itself
  SELECT ...
  FROM name JOIN ...
)
SELECT * FROM name;

اجتياز مخطط المؤسسة

WITH RECURSIVE reports AS (
  -- anchor: the manager themself
  SELECT id, full_name, manager_id, 0 AS depth
  FROM employees WHERE id = 42

  UNION ALL

  -- recurse: people whose manager is in reports
  SELECT e.id, e.full_name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON r.id = e.manager_id
)
SELECT * FROM reports ORDER BY depth, full_name;

التعليقات المتداخلة

WITH RECURSIVE thread AS (
  SELECT id, parent_id, body, 0 AS depth, ARRAY[id] AS path
  FROM comments WHERE id = $1
  UNION ALL
  SELECT c.id, c.parent_id, c.body, t.depth + 1, t.path || c.id
  FROM comments c
  JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread ORDER BY path;

الإنهاء

تجنّب الحلقات اللانهائية

WITH RECURSIVE walk AS (
  SELECT id, ARRAY[id] AS path FROM nodes WHERE id = $1
  UNION ALL
  SELECT e.target_id, w.path || e.target_id
  FROM edges e
  JOIN walk w ON e.source_id = w.id
  WHERE e.target_id <> ALL(w.path)
)
SELECT * FROM walk;

سلسلة رقمية

WITH RECURSIVE n(i) AS (
  VALUES (1)
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 100
)
SELECT i, i*i AS square FROM n;

قائمة مكوّنات المنتج

WITH RECURSIVE bom AS (
  SELECT part_id, sub_part_id, qty FROM parts WHERE part_id = $1
  UNION ALL
  SELECT p.part_id, p.sub_part_id, p.qty * bom.qty
  FROM parts p
  JOIN bom ON bom.sub_part_id = p.part_id
)
SELECT sub_part_id, SUM(qty) AS total_qty FROM bom GROUP BY sub_part_id;

حدود العمق

WITH RECURSIVE tree AS (
  SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = $1
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id
  WHERE t.depth < 10
)
SELECT * FROM tree;

UNION مقابل UNION ALL

الأداء

مراجعة

تحقق سريع