كيف تعمل CTEs والاستعلامات العودية في PostgreSQL؟

Question

Accepted Answer

**CTE** (Common Table Expression)، المعرفة بـ **`WITH`**، هي مجموعة نتائج مؤقتة مسماة تجعل الاستعلامات المعقدة سهلة القراءة. يدعم PostgreSQL بالكامل CTEs، بما في ذلك **العودية** CTEs (`WITH RECURSIVE`) لاجتياز البيانات الهرمية/الشجرية.

## CTEs الأساسية — بناء استعلام قابل للقراءة

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

تقسم CTEs الاستعلام المعقد إلى خطوات مسماة وقابلة للقراءة (مسلسلة، كل منها يشير إلى الخطوات السابقة) — أوضح بكثير من الاستعلامات الفرعية المتداخلة.

## CTEs العودية — اجتياز الهرميات

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

تحتوي **CTE عودية** على حالة أساسية (الصفوف الأولية) وخطوة عودية (`UNION ALL` التي تنضم مرة أخرى إلى CTE نفسها)، وتتكرر حتى لا تظهر صفوف جديدة — مثالية لـ **الأشجار والهرميات** (الهياكل التنظيمية، أشجار الفئات، فاتورة المواد، مسارات الرسم البياني).

## ملاحظة حول تجسيد CTE (خاص بـ Postgres)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

قبل Postgres 12، كانت CTEs دائماً "سياج التحسين" (مجسدة)؛ الآن يمكن دمجها لتحسين الأداء — `MATERIALIZED`/`NOT MATERIALIZED` يتحكم في هذا.

## لماذا يهم

CTEs والاستعلامات العودية هي ميزات قيمة في PostgreSQL لـ **سهولة القراءة والبيانات الهرمية**، لذا فهم هذه المزايا مفيد لكتابة استعلامات معقدة قابلة للصيانة ومعالجة البيانات ذات الهيكل الشجري.

الفائدة اليومية هي **سهولة القراءة**: الاستعلامات المعقدة التي تحتوي على مستويات متعددة من الاستعلامات الفرعية تصبح صعبة الصيانة، بينما تتيح لك CTEs تحليلها إلى خطوات منطقية مسماة ومتسلسلة (قراءة من الأعلى إلى الأسفل)، مما يحسن الوضوح بشكل كبير — مما يجعل CTEs أداة مهمة لكتابة SQL قابل للصيانة.

القدرة البارزة هي **CTEs العودية** (`WITH RECURSIVE`)، التي تمكن من الاستعلام عن **البيانات الهرمية وذات الهيكل الشجري** (الهياكل التنظيمية، أشجار الفئات، فاتورة المواد، التعليقات المترابطة، اجتياز الرسم البياني) — وهي فئة من الاستعلامات التي يصعب جداً تنفيذها في SQL بطريقة أخرى، مما يجعل CTEs العودية الحل القياسي لاجتياز البيانات الهرمية.

فهم بنية CTE العودية (الحالة الأساسية + الخطوة العودية التي تنضم مرة أخرى إلى نفسها) هو معرفة أساسية لهذه الحاجة الشائعة.

التفصيل المفيد والخاص بـ Postgres هو سلوك **تجسيد CTE** (Postgres 12+ يمكنه دمج CTEs لتحسين الأداء، قابل للتحكم بـ `MATERIALIZED`/`NOT MATERIALIZED`) — ذات صلة بالتحسين.

نظراً لأن الاستعلامات المعقدة شائعة (وتهم سهولة القراءة للصيانة)، والبيانات الهرمية (التي تتعامل معها CTEs العودية بشكل فريد) تظهر بتكرار (الهياكل التنظيمية، الفئات المتداخلة)، فإن فهم CTEs والاستعلامات العودية معرفة قيمة وعملية في PostgreSQL تحسن قابلية صيانة الاستعلام وتفتح قدرات الاستعلام الهرمي — وهي قدرة ذات صلة متكررة لـ SQL نظيف ومتطور وموضوع شائع، خاصة الشكل العودي للحاجة المتكررة لاستعلام البيانات ذات الهيكل الشجري.