I have the following two tables
# Event table
id title start_date end_date
----------------------------------------------------------
1 Foo 2013-05-06 20:30:00 2013-05-06 22:45:00
2 Bar 2013-06-03 07:40:00 2013-05-06 10:45:00
3 Demo 2013-05-02 00:22:00 2013-05-06 03:45:00
4 Test 2013-07-09 10:56:00 2013-05-06 13:45:00
# Notification table
id event_id mail time type sent
----------------------------------------------------------
1 1 123@example.com 15 minute 0
2 1 234@example.com 01 day 0
3 3 655@example.com 01 week 0
4 4 888@example.com 40 minute 0
5 4 999@example.com 42 minute 1
5 4 987@example.com 30 minute 0
Every minute a cron job executes a PHP script to check if a notification should be sent to the email address in the notification table. The time value is the relative time before the start date of the event when the reminder should be sent.
For example when the cron job runs at 2013-05-06 20:15:00 an email should be sent to the email address 123@example.com. A sent flag should also be considered. If this is set to 0 then all notifications should be sent also if it is lower than the send date. For example when the cron job runs at 2013-07-09 10:26:00 a mail should be sent to 987@example.com and 888@example.com.
My question is, how does the query have to look like in mysql to fetch the notifications to meet the requirements from above for the actual (now()) date? I tried with date_sub but was not really successful. An help is really appreciated.
There are two parts to this problem, as far as I can tell. One is converting the different units into time comparisons. The other is making sure that only one notification goes out.
The first can be handled by a big case statement. The second can be handled by checking for an interval on the time, instead of an inequality:
. . .
from event e join
notification n
on e.id = n.event_id
where (case when n.type = 'minute'
then now() - interval n.time minute <= e.start_date and
now() + interval 1 minute - interval time minute > e.start_date
when n.type = 'day'
then now() - interval n.time day <= e.start_date and
now() + interval 1 minute - interval time day > e.start_date
when n.type = 'week'
then now() - interval 7*n.time day <= e.start_date and
now() + interval 1 minute - interval 7*time day > e.start_date
when n.type = 'month'
then now() - interval n.time month <= e.start_date and
now() + interval 1 minute - interval time month > e.start_date
end) > 0 and sent = 0;
And, as Tom points out, it would be better to have a sent flag for each notification, rather than depending on the particular time. Jobs can be delayed for all sorts of reasons.