ניהול מודעות בSQL
-
אני בונה מערכת לפרסום דירות, כחלק מהפרוייקט אני צריך לתת למאזין להרשם כמנוי לצינתוקים לפי סינון לדוגמא דירות בירושלים, דירות 4 חדרים ברמת אלחנן שבבני ברק. וכמובן צריך שתהיה אפשרות לסנן מה המאזין שמע כבר ומה לא.
מאגר לנתוני הדירות עשיתי עם כל הלוגיקה של הכנסה, מציאת התאמה, שליפה, עדכון ומחיקה.
השאלה איך עושים את הקטע של המאזינים?
אני יכול להוסיף מסד נתונים שיכיל מספר טלפון ואת הנתונים האפשריים וכשיש מודעה חדשה להשוות אותה לנתונים האלו ואם יש התאמה לצנתק לאותו אחד. זה נכון?
איך עושים את ניהול המודעות? מה הוא שמע ומה לא? -
אתה עושה טבלה נפרדת, ששמה יש מה הוא שמע, נניח נקרא לה ModhaToPhone, יש בה שלוש עמודות: ModhaId, Phone, At שזה מזהה מודעה, מזהה מאזין - מספר טלפון ותאריך.
בשליפת הסינון אתה מחבר בין הטבלה של הדירות (מודעות) לטבלת הדירה_מאזין עם LEFT JOIN עם התניה שדירה_מאזין ריק, ככה:SELECT * FROM Modaot LEFT JOIN ModhaToPhone ON ModhaToPhone.ModhaId = Modaot.Id AND ModhaToPhone.Phone = '050xxxx' WHERE ModhaToPhone.ModhaId IS NULL
השאילתה הזאת לוקחת מטבלת המודעות רק שורות שאין להם שורה תאומה בטבלת הModhaToPhone עם הטלפון של המתקשר הנוכחי, מה שמבטיח שהוא לא יקבל תוצאות שהוא כבר שמע.
נוסח שונה לשליפה:
SELECT * FROM Modaot WHERE NOT EXISTS( SELECT 1 FROM ModhaToPhone WHERE ModhaToPhone.ModhaId = Modaot.Id AND ModhaToPhone.Phone = '050xxxx' )
כמובן שאחרי השליפה והשמעה יש להכניס לטבלת ההשמעות את המודעות.
-
טוב, יש עוד פרט.
אני צריך גם שיהיה סינון ולא כל ההודעות יושמעו, יש עוד טבלה שבה ההעדפוץ של כל אחד, לפי עיר/עיר+שכונה/עיר+שכונה+רחוב.
איך משלבים את כל הסיפור הזה ביחד?
יש לי קוד מושך את כל ההעדפות של המאזיןSELECT * FROM ********** WHERE phone = ?
(כמובן זה קובץ בפני עצמו יש בדיקות לפני ואחרי, וכל שאר המימוש, זה רק הקוד הרלוונטי).
האם צריך לקרוא לזה ולפי מה שמתקבל לבנות שאילתות או שיש דרך לשלב הכל בבת אחת.
-
ז"א שאני קודם עושה בקוד
SELECT * FROM ********** WHERE phone = ?
ואז לפי ההעדפות שקבלתי אני עושה AND, לדוגמא אם ההעדפות נשמרות בresult והם דירה בירושלים בשכונה X 4 חדרים ודירה בביתר עילית 3 חדרים אז אני עושה:
SELECT * FROM Modaot WHERE NOT EXISTS ( SELECT 1 FROM ModhaToPhone WHERE ModhaToPhone.ModhaId = Modaot.Id AND ModhaToPhone.Phone = ? ) AND ( (Modaot.city = ? AND Modaot.region = ? AND Modaot.room = ?) OR (Modaot.city = ? AND Modaot.room = ?) )
?
-
אפשר הכל בשאילתה אחת (אם כי מסיבות אפליקטיביות לפעמים יש עניין לפצל):
SELECT * FROM Modaot CROSS JOIN (SELECT * FROM UserPreference WHERE phone = ?) AS PR --העדפות משתמש WHERE NOT EXISTS ( SELECT 1 FROM ModhaToPhone WHERE ModhaToPhone.ModhaId = Modaot.Id AND ModhaToPhone.Phone = ? ) AND (Modaot.city = PR.city OR PR.city IS NULL) AND --סינון ישיר מול הערך של ההעדפה בטבלת ההעדפות (Modaot.region = PR.region OR PR.region IS NULL)
-
זו הצעה של GPT
SELECT M.* FROM Modaot M WHERE NOT EXISTS ( SELECT 1 FROM ModhaToPhone MT WHERE MT.ModhaId = M.Id AND MT.Phone = ? ) AND EXISTS ( SELECT 1 FROM UserPreference PR WHERE PR.phone = ? AND (M.city = PR.city OR PR.city IS NULL) AND (M.region = PR.region OR PR.region IS NULL) AND (M.room = PR.room OR PR.room IS NULL) )
מה אומרים?