WITH RECURSIVE في SQLite: استعلامات الأشجار والتسلسل

التكرار في SQL يبدو غريبًا… حتى تراه يعمل أمام عينيك

معظم الاستعلامات التي نكتبها تُرجع صفوفًا من بيانات موجودة أصلًا. لكن الـ CTE التكراري في SQLite يعمل بطريقة مختلفة تمامًا: فهو يبني الصفوف خطوة بخطوة، ويُمرّر مخرجاته إلى نفسه كمدخلات جديدة، ويستمر هكذا حتى لا يبقى شيء جديد ليُضاف. بهذا الأسلوب تستطيع التنقّل في شجرة لا تعرف عمقها مسبقًا، أو توليد سلسلة الأرقام من 1 إلى 100 دون الحاجة إلى جدول جاهز للأرقام.

والصيغة العامة ثابتة دائمًا، كما يلي:

WITH RECURSIVE name(columns) AS (
    -- نقطة الارتكاز: الصفوف الابتدائية
    SELECT ...
    UNION ALL
    -- التكرار: الصفوف المشتقة من الخطوة السابقة
    SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

الجزء المرجعي في الأعلى، ثم UNION ALL، وتحته الاستعلام التكراري. يبدأ SQLite بتشغيل الجزء المرجعي مرة واحدة، ثم يواصل تنفيذ الجزء التكراري — مستخدمًا في كل جولة الصفوف الناتجة من الجولة السابقة — إلى أن يتوقف عن إنتاج صفوف جديدة. عند تلك اللحظة يتوقف الاستعلام.

توليد سلسلة أرقام من 1 إلى 10 في SQLite

أبسط مثال على CTE تكراري هو توليد سلسلة أرقام، دون الحاجة إلى أي جدول:

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

تتبّع خطوات التنفيذ:

1. الجزء الابتدائي (Anchor) يُنتج صفًا واحدًا: n = 1.
2. الخطوة التكرارية تأخذ هذا الصف، وتحسب n + 1 = 2، وبما أن 2 < 10 صحيح، فإنها تُبقي الصف.
3. التكرار التالي يأخذ n = 2 ويُنتج n = 3. وهكذا.
4. عندما تصل n إلى 10، يصبح 10 < 10 خاطئًا، فلا تُرجع الخطوة التكرارية أي صفوف، ويتوقف SQLite.

شرط WHERE n < 10 هو شرط الإيقاف. وبدونه يدخل الاستعلام في حلقة لا نهائية ولن يتوقف أبدًا.

توليد سلسلة من التواريخ في SQLite

نفس الفكرة، لكنها مفيدة جدًا في التقارير الواقعية — مثل ملء كل يوم ضمن نطاق زمني معيّن، حتى الأيام التي لم يحدث فيها شيء:

WITH RECURSIVE days(d) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;

عادةً ما تستخدم LEFT JOIN لربط هذه السلسلة بجدول الأحداث حتى تتمكن من احتساب الأيام الخالية من الأحداث بشكل صحيح. فلو استخدمت GROUP BY date لوحدها، ستتجاهل الأيام الفارغة تمامًا، أما سلسلة التواريخ فتمنحك صفًا لكل يوم بغض النظر عن وجود أحداث أم لا.

التنقل عبر شجرة الأب والابن

هذه هي حالة الاستخدام الكلاسيكية لاستعلام شجري في SQLite. لدينا هنا جدول موظفين، حيث يشير كل صف إلى المدير المسؤول عنه:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE org(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;

يبدأ الجزء الثابت (Anchor) من الجذر، أي الشخص الذي ليس له مدير فوقه. ثم يأتي الجزء التكراري ليربط جدول الموظفين مرة أخرى بالـ CTE، فيلتقط كل موظف يطابق manager_id الخاص به قيمة id موجودة فعلًا داخل الـ CTE. كل دورة تكرار تنزل مستوى واحدًا أعمق في الشجرة. أما depth فهو مجرد عدّاد أضفناه لكي نُزيح المخرجات ونوضّح التدرّج.

هذه الطريقة تعمل مع أي شجرة مهما كان عمقها. سواء كانت مستويين أو عشرة مستويات، الاستعلام يبقى كما هو دون أي تعديل.

إيجاد جميع الأسلاف لصف معيّن

الآن لنعكس الاتجاه. بدلًا من النزول من الجذر إلى الأسفل، سنصعد إلى الأعلى انطلاقًا من موظف محدد لنحصل على سلسلة المدراء فوقه كاملةً:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE chain(id, name, manager_id) AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Diego'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;

نقطة البداية (anchor) هي الموظّف الذي ننطلق منه، ثم تقفز كلّ خطوة تكرارية إلى المدير الأعلى. ويتوقّف SQLite تلقائياً عند الوصول إلى الجذر، أي حين تكون قيمة manager_id IS NULL، لأنّ عملية الـ JOIN لن تجد أيّ صف مطابق بعد ذلك.

هذا النمط مفيد جداً في حالات مثل: مسارات التنقّل (breadcrumbs)، والتعليقات المتشعّبة، ومسارات التصنيفات، وأي حالة تحتاج فيها إلى "الصعود حتى القمّة".

شروط التوقّف والحلقة اللا نهائية في CTE

أشهر خطأ يقع فيه المطوّرون هو نسيان شرط التوقّف أو كتابته بطريقة لا تتحقّق أبداً، فيدخل الاستعلام في حلقة لا نهائية. لاحظ الفرق بين الحالتين:

-- يعمل إلى ما لا نهاية:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

لا توجد جملة WHERE تُعيد صفر صفوف في أي لحظة، وبالتالي فإن SQLite سيستمر بكل بساطة في العدّ إلى ما لا نهاية.

عادتان وقائيتان ينبغي اتباعهما:

1. ضع دائمًا جملة WHERE في الجزء التكراري تحدّ من النمو.
2. أضف LIMIT إلى SELECT الخارجي كشبكة أمان أثناء التطوير — فحتى لو أخطأت في تقدير شرط التوقف، سينتهي الاستعلام على أي حال.

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter
)
SELECT n FROM counter LIMIT 5;

الـ CTE نفسه غير محدود، لكن LIMIT 5 يوقف الاستعلام الخارجي مبكرًا. الجميل أن SQLite ذكي بما يكفي فلا يستمر في التكرار بعد ما يحتاجه LIMIT. هذه طريقة مفيدة للاستكشاف فقط، ولا تُغني أبدًا عن شرط توقف حقيقي في كود الإنتاج.

التعامل مع الدورات في الرسوم البيانية

الأشجار بطبيعتها لا تحتوي على دورات، أما الرسوم البيانية العامة فقد تحتوي عليها — وهنا أي CTE تكراري ساذج سيقع في حلقة لا نهائية إذا كانت البيانات تحوي دورة. الحل أن تتتبع المسار الذي قطعته حتى الآن وتمنع زيارة العقد التي مررت بها مسبقًا:

CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);

WITH RECURSIVE walk(node, path) AS (
    SELECT 1, ',1,'
    UNION ALL
    SELECT e.b, path || e.b || ','
    FROM edges e
    JOIN walk ON e.a = walk.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;

path عبارة عن نص يحتوي على العُقد التي زرناها مفصولة بفواصل. قبل إضافة أي عقدة جديدة، تتحقق جملة WHERE من أنها ليست موجودة فيه أصلاً. لولا هذا الشرط، لدارت الحلقة 1 → 2 → 3 → 1 إلى ما لا نهاية.

لا توجد في SQL بنية جاهزة باسم "visited set" — عليك أن تبنيها بنفسك، إما كسلسلة نصية أو عبر ربط الجدول بالـ CTE نفسه حتى تلك اللحظة.

CTE تكراري أم Self Join؟

إن كنت تحتاج مستوى أو مستويين فقط من العمق، فإن الـ self join أبسط وأسرع:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

هذا يكفي لمعرفة "من هو المدير المباشر لكل شخص". لكن إذا أردت "كل من يتبع آدا في التسلسل الإداري مهما كان العمق" — والعمق غير معروف مسبقًا — فلا يوجد حل نظيف غير الـ CTE التكراري. اختر الأداة التي تناسب العمق المطلوب:

• عمق محدود وصغير: استخدم self join، ربما اثنين أو ثلاثة.
• عمق غير معروف أو مفتوح: استخدم WITH RECURSIVE.

الفكرة الذهنية وراء الـ CTE التكراري

الـ CTE التكراري في SQLite هو في جوهره حلقة تكرارية، لكن مكتوبة بأسلوب تصريحي:

• المرساة (anchor) هي القيمة الابتدائية للحلقة.
• الاستعلام التكراري هو جسم الحلقة — يُنتج الدفعة التالية من الصفوف اعتمادًا على الصفوف الحالية.
• شرط التوقف هو اختبار الخروج من الحلقة — حين يُرجع صفرًا من الصفوف، تنتهي الحلقة.
• UNION ALL يُجمّع كل النتائج في مجموعة النتائج النهائية.

بمجرد أن ترسخ هذه الصورة في ذهنك، تتوقف الصياغة عن الإحساس بالغرابة. أنت ببساطة تكتب حلقة for بصيغة SQL.

الخطوة التالية: الفهارس

الـ CTE التكراري يمر على عدد كبير من الصفوف، والـ join داخل الخطوة التكرارية يُنفَّذ في كل دورة. إن لم يكن العمود المستخدم في الـ join مفهرسًا، سينهار الأداء بسرعة كبيرة. الفهارس هي موضوع الفصل التالي، والعمود manager_id مثال نموذجي على عمود يستفيد من الفهرسة بشكل واضح.