mysql date_sub using a field as interval

I need help with mysql and date_sub(). I have a table call Activity

Activity(id,deadline,alert)
Activity(1,'2011-04-18','1 DAY');
Activity(2,'2011-04-13','1 MONTH');

Every row in A have an 'alert', this field indicate how time before the deadline an activity have to reported.

For example

On 2011-04-17 I have to report the activity with 'id' 1
On 2011-03-14 I have to report the activity with 'id' 2

I trying to use date_sub() functions, but I can't use a field as params of this function. Any idea how to fix this?

SELECT * 
  FROM `activities` 
 WHERE date_sub(`deadline`, INT开发者_StackOverflowERVAL alert) >= CURDATE();

---

Split the alert into 2 fields

Alert_count: integer
Alert_period: enum('hour','day','month','week')

And change the query like so:

SELECT * 
  FROM `activities` 
 WHERE CASE alert_period 
   WHEN 'hour' THEN date_sub(`deadline`, INTERVAL alert_count HOUR) >= CURDATE();
   WHEN 'day' THEN date_sub(`deadline`, INTERVAL alert_count DAY) >= CURDATE();
   ...
 END CASE

---

If the number of alerts is small, you could write out a case:

WHERE case 
      when alert = '1 DAY' then date_sub(`deadline`, INTERVAL 1 DAY) 
      when alert = '1 MONTH' then date_sub(`deadline`, INTERVAL 1 MONTH) 
      ... etc ...
      end >= CURDATE();

---

Although this solution will work it's not the most efficient way of storing this data because each time you query for this data MySQL must look at the interval value in every row, compute it against deadline date and then return you the answer.

If you were to compute this information just before you insert the data and store alert_date as a DATE column then (assuming you index it too) it'd be very fast for MySQL to find the rows with a query like:

SELECT id FROM activity WHERE alert=CURRENT_DATE();

even more efficient (it'd allow it to be query cached):

SELECT id FROM activity WHERE alert="2011-04-23";

---

Strings are not allowed after INTERVAL, you can convert your all alert limit to day on one column.

Activity(id,deadline,alert)
Activity(1,'2011-04-18','1');
Activity(2,'2011-04-13','30'); 

and use as:

SELECT * 
  FROM `activities` 
 WHERE date_sub(`deadline`, INTERVAL alert DAY) >= CURDATE();