[MySQL] Eén van twee datums icm. WHERE BETWEEN

[TheNephilim]

Na verschillende problemen opgelost te hebben, kom ik weer wat gekke dingen tegen waar ik geen oplossing voor kan vinden. Misschien heb ik het verkeerd aangepakt, maar dat zal dan wel blijken.

Een reminder bestaat uit een start datum en een periode. Op de start datum + periode krijg je een notificatie. Dan kan er een history entry toegevoegd worden, als er iets met de 'herinnering' gedaan is. Op dat moment word er voor een nieuwe notificatie de datum van de laatste history entry gepakt + de periode van de reminder zelf.

Dat werkt allemaal, maar nu wil ik een selectie maken van de reminders binnen een bepaalde herinneringsperiode. Helaas gaat dat niet helemaal goed, maar het probleem lijkt niet eenduidig. Soms met en soms zonder history entries, of zelfs gewoon allebei. Dus klopt wel wat ik in onderstaande (versimpelde) query doe? Sowieso de verschillende terugkomende subselects lijken me niet erg handig, maar hoe anders?

SQL:

SELECT
    reminder.id,
    reminder.start_date,
    reminder.period,
    (SELECT 
        reminder_history.date 
        
    FROM 
        reminder_history

    WHERE
        reminder_history.reminder_id = reminder.id

    ORDER BY
        reminder_history.date DESC LIMIT 1

    ) AS latest_reminder_history_date

FROM
    reminder

WHERE
    IF (
        (SELECT reminder_history.date FROM reminder_history WHERE reminder_history.reminder_id = reminder.id ORDER BY reminder_history.date DESC LIMIT 1) IS NULL,
        DATE_ADD(reminder.start_date, INTERVAL reminder.period DAY),
        DATE_ADD((SELECT reminder_history.date FROM reminder_history WHERE reminder_history.reminder_id = reminder.id ORDER BY reminder_history.date DESC LIMIT 1), INTERVAL reminder.period DAY)
    ) BETWEEN '2014-1-1' AND '2014-12-31'

ORDER BY
    IF (
        latest_reminder_history_date IS NULL,
        DATE_ADD(reminder.start_date, INTERVAL reminder.period DAY),
        DATE_ADD(latest_reminder_history_date, INTERVAL reminder.period DAY)
    ) ASC

Het ORDER BY gedeelte werkt prima, maar nu ik een BETWEEN toevoeg gaat het fout. Ik denk dat het IF statement niet goed werkt, maar is er een andere manier om dit te doen?

[Verwijderd]

waarom outer join je niet een inline view met deze waarden: SELECT reminder_id, MAX(date) date FROM reminder_history GROUP BY reminder_id. Dan hoef je in je WHERE en ORDER BY alleen maar een IFNULL te doen op de date kolommen van beide tabellen.

In Oracle SQL had ik het met een WITH statement gedaan. Misschien bestaat er ook wel zoiets voor MySQL

Nu voer je drie keer de zelfde subquery uit...

[TheNephilim]

Oké dat klinkt heel logisch. Moet ik in MySQL dan UNION gebruiken? OUTER JOIN heb je in MySQL niet toch?

[RobIII]

TheNephilim schreef op dinsdag 19 augustus 2014 @ 12:55:
OUTER JOIN heb je in MySQL niet toch?

Toch...

[TheNephilim]

RobIII schreef op dinsdag 19 augustus 2014 @ 13:36:
[...]

Toch...

Dat heb ik even bekijken, een RIGHT en LEFT en dan op elkaar met een UNION, zo begreep ik het. Echter moeten beide dan evenveel velden hebben en krijg je dus nog meer joins etc...

Nu heb ik het zo opgelost:

SQL:

SELECT
    reminder.id,
    reminder.start_date,
    reminder.period,
    reminder_history.date

FROM
    reminder

    LEFT JOIN reminder_history ON (reminder_history.id = (SELECT reminder_history.id FROM reminder_history WHERE reminder_history.reminder_id = reminder.id ORDER BY reminder_history.date DESC LIMIT 1))


WHERE
    IF (
        reminder_history.date IS NULL,
        DATE_ADD(reminder.start_date, INTERVAL reminder.period DAY),
        DATE_ADD(reminder_history.date, INTERVAL reminder.period DAY)
    ) BETWEEN '2014-1-1' AND '2014-12-31'

ORDER BY
    IF (
        reminder_history.date IS NULL,
        DATE_ADD(reminder.start_date, INTERVAL reminder.period DAY),
        DATE_ADD(reminder_history.date, INTERVAL reminder.period DAY)
    ) ASC

Dat heeft het geheel een stuk leesbaarder gemaakt en ik heb nu maar één subselect.

Toch gaat er nog iets mis, als de reminder.start_date jonger is dan de reminder_history.date. Zou niet moeten uitmaken, want zolang er een history entry is, zou hij die moeten pakken en niet de reminder.start_date.

---

Als ik bovenstaande query uitvoer, met die datums, dan krijg ik het record met ID 12 uit onderstaande niet te zien.



Zonder BETWEEN wel weer...

[ Voor 6% gewijzigd door TheNephilim op 19-08-2014 13:59 ]

[TheNephilim]

Opgelost! De WHERE statement ziet er nu zo uit:

SQL:

(CASE
    WHEN reminder_history.date IS NULL
    THEN DATE_ADD(reminder.start_date, INTERVAL reminder.period DAY)
    ELSE DATE_ADD(reminder_history.date, INTERVAL reminder.period DAY) 
END BETWEEN '2014-1-1' AND '2014-12-31')

[ Voor 7% gewijzigd door TheNephilim op 19-08-2014 15:03 ]

[Verwijderd]

Zonder het getest te (kunnen) hebben, dacht ik meer aan onderstaande. Ik weet niet of het qua syntaxis 100% is, maar het gaat even om de inline view en de outer join.

Ik zou er nog wel even een sql cursusje tegenaan gooien. Je moet imho wel weten wat outer joins zijn

SQL:

SELECT
    reminder.id,
    reminder.start_date,
    reminder.period,
    reminder_hist.date

FROM
    (SELECT MAX(date) AS date, reminder_id FROM reminder_history GROUP BY reminder_id) reminder_hist

    RIGHT OUTER JOIN reminder ON (reminder_hist.reminder_id = reminder.reminder_id)


WHERE ( DATE_ADD ( IFNULL (reminder_hist.date, reminder.start_date), INTERVAL reminder.period DAY ) ) BETWEEN '2014-1-1' AND '2014-12-31'

ORDER BY ( DATE_ADD ( IFNULL (reminder_hist.date, reminder.start_date), INTERVAL reminder.period DAY ) ) ASC

[TheNephilim]

Op deze manier een query opbouwen heb ik nog nooit gedaan, de FROM een subselect en dan een RIGHT OUTER JOIN naar de tabel waar het eigenlijk om gaat.

Als ik deze week tijd heb, ga ik zeker eens proberen of dit ook (en beter) werkt.

Vroegah deed ik best veel met SQL, maar tegenwoordig niet. Het is een beetje verstoft denk ik