ניהול מודעות בSQL

[eido]

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

[dovid]

אתה עושה טבלה נפרדת, ששמה יש מה הוא שמע, נניח נקרא לה 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'
)

כמובן שאחרי השליפה והשמעה יש להכניס לטבלת ההשמעות את המודעות.

[eido]

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

SELECT * FROM ********** WHERE phone = ?

(כמובן זה קובץ בפני עצמו יש בדיקות לפני ואחרי, וכל שאר המימוש, זה רק הקוד הרלוונטי).

האם צריך לקרוא לזה ולפי מה שמתקבל לבנות שאילתות או שיש דרך לשלב הכל בבת אחת.

[dovid]

בטח לשלב.
שים AND עם התנאי שהראיתי, ובסגנון הראשון עליך להוסיף גם את הLEFT JOIN אחרי הFROM.

[eido]

ז"א שאני קודם עושה בקוד

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 = ?)
)

?

[dovid]

אפשר הכל בשאילתה אחת (אם כי מסיבות אפליקטיביות לפעמים יש עניין לפצל):

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) 

[eido]

הענין הוא שיכולות להיות כמה העדפות, כמו בדוגמא, דירה בירושלים 4 חדרים ובביתר 3 חדרים, זה שתי שורות שונות במסד נתונים.

[dovid]

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

[eido]

זו הצעה של 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)
)

מה אומרים?

[dovid]

@eido נראה טוב.