stack stack - 5 months ago 6
MySQL Question

How can I count something before inserting?

I have this table:

// cookies
+---------+-------------------------+------------------+------------+
| id | email | cookie | date_time |
+---------+-------------------------+------------------+------------+
| int(11) | varchar(50) | varchar(128) | int(11) |
+---------+-------------------------+------------------+------------+
| 1 | jack_2009@gmail.com | ojer0f934mf2... | 1467204523 |
| 2 | peter.zm@yahoo.com | ko4398f43043... | 1467205521 |
| 3 | matrix_john23@gmail.com | 34fjkg3j438t... | 1467205601 |
| 4 | peter.zm@yahoo.com | 0243hfd348i4... | 1467206039 |
+---------+-------------------------+------------------+------------+


And here is my query:

INSERT INTO cookies VALUES(NULL, $email, $hash, unix_timestamp())


Now I need to check following condition before inserting:

The number of rows (for specific user) should be less than:


  • 5
    per hour

  • 10
    per day

  • 50
    per month

  • 100
    per total






I just can check the last case:

INSERT INTO cookies(id, email, cookie, date_time)
SELECT NULL, $email, $hash, unix_timestamp()
FROM cookie
WHERE email = $email AND
100 >= ( SELECT count(1) FROM cookies WHERE email = $email )


Well, how can I add other conditions?

Answer

I'm not positive on whether the >'s (in the group by) should be >='s, but I think this will do what you are asking.

INSERT INTO cookies(id, email, cookie, date_time)
SELECT NULL, $email, $hash, unix_timestamp()
FROM cookie
WHERE email = $email 
   AND NOT EXISTS (
        SELECT COUNT(CASE WHEN date_time > UNIX_TIMESTAMP(now() - INTERVAL 1 HOUR) 
                          THEN 1 ELSE NULL END) AS rowsInLastHour
           , COUNT(CASE WHEN date_time > UNIX_TIMESTAMP(now() - INTERVAL 1 DAY) 
                          THEN 1 ELSE NULL END) AS rowsInLastDay
           , COUNT(CASE WHEN date_time > UNIX_TIMESTAMP(now() - INTERVAL 1 MONTH) 
                        THEN 1 ELSE NULL END) AS rowsInLastMonth
           , COUNT(1) AS rowsEver
        FROM cookie
        WHERE email = $email
        HAVING rowsInLastHour > 5 
          OR rowsInLastDay > 10 
          OR rowsInLastMonth > 50 
          OR rowsEver > 100
    )
;
  • It counts all the rows (for the email) that had date_time values in the last hour|day|month by using now() - INTERVAL 1 HOUR|DAY|MONTH to find when the last hour|day|month started, and counting those values that occurred after those starting times.
  • It then uses the HAVING to only yield the singular result (aggregation such as COUNT that does not have an associated GROUP BY clause always results in 1 row), if any of the limits you specified were exceeded.
  • Then the NOT EXISTS returns true if there were no results (because the limits were not exceeded).

Edit: Updated comparisons to use unit timestamps, as needed by question.

Comments