אחד הדברים שלא נכנסו לקורס SQL החדש שכתבתי היה שאילתות רקורסיביות. וכן מאוד רציתי להקליט עליהן שיעור אבל הרגשתי שזה יותר מדי בשביל קורס SQL בסיסי, אז בואו נראה אותן בפוסט במקום.

1. יצירת הנתונים

אני מתחיל עם טבלה של אנשים, בשביל הדוגמה מספיק רק שמות ומזהים:

create table people(id integer primary key, name string);

insert into people(id, name) values
(1, 'Brian'),
(2, 'Bob'),
(3, 'John'),
(4, 'Mike'),
(5, 'Frank'),
(6, 'Sarah'),
(7, 'Layla'));

ואז אני יוצר טבלת חיבור רק שבמקום לחבר בין טבלת people לטבלה אחרת, טבלת החיבור שלי מחברת בין people לבין עצמה - כלומר זו טבלת חברים שכל שורה בה אומרת ששני אנשים הם חברים אחד של השני:

create table friends(id integer primary key, person_1 integer, person_2 integer);

insert into friends(person_1, person_2) values
(3, 5),
(3, 7),
(6, 7),
(2, 4);

קל לראות שיש לנו בנתונים שתי קבוצות חברים - בקבוצה אחת יש את ג'ון, פרנק, שרה וליילה (מזהים 3, 5, 6 ו-7) ובקבוצה השנייה את בוב ומייק (מזהים 2 ו-4).

עכשיו בואו נראה איך בסיס הנתונים יכול לזהות את שתי הקבוצות האלה.

2. חיפוש חברים

בשביל לחפש חברים אני צריך להתחיל ממזהה של בן אדם ולחפש איפה הוא מופיע בטבלת החברים - אם המזהה הזה מופיע בשדה person_1 אז אני לוקח את person_2 בתור חבר, אם המזהה שלי מופיע בעמודה person_2 אז עמודת person_1 היא מזהה החבר שלו. בדוגמה שלנו השורה הראשונה בטבלת החברים אומרת ש 3 הוא חבר של 5, ולכן אם אני מתחיל לחפש חברים של 3 אני קודם כל מוסיף לחבורה את 5. אחרי זה בשורה השנייה אני מוסיף את 7 ובשורה השלישית אני מזהה ש 7 ו-6 חברים, 7 כבר נמצא אצלי באוסף התוצאות ולכן אני מוסיף גם את 6.

התרגום של זה ל SQL נראה ככה:

WITH RECURSIVE nodes(x) AS (
   SELECT 3
   UNION
   SELECT friends.person_1 FROM friends JOIN nodes ON friends.person_2 = nodes.x
   UNION
   SELECT friends.person_2 FROM friends JOIN nodes ON friends.person_1 = nodes.x
)
SELECT nodes.x, people.name FROM nodes
inner join people
on people.id = nodes.x;

הפקודה WITH RECURSIVE מגדירה "לולאה" ב SQL, ובסוגריים אני כותב מזהה לעמודה. במקרה שלנו הלולאה אוספת תוצאות רק בעמודה אחת והעמודה נקראת x. אני מתחיל עם הערך 3 ומשתמש ב UNION כדי להמשיך את הלולאה.

ה SELECT הראשון מחפש שורות שעמודת ה person_1 שלהן נמצאת ברשימת התוצאות שלי, וה SELECT השני מחפש את השורות שעמודת ה person_2 נמצאת ברשימת התוצאות. למעשה כל פעם שאחד ה SELECT-ים מהשלושה מחזיר משהו חדש בסיס הנתונים ישתמש ברשימת התוצאות החדשה שנוצרה כדי לחפש עוד תוצאות, וימשיך בזה עד שכבר לא יהיו תוצאות חדשות.

בצורה כזאת השאילתה מחזירה את החבורה הראשונה - זו שכוללת את פרנק, ג'ון, ליילה ושרה. ואיך מוצאים את החברים של בוב? את זה אתן לכם לגלות לבד.