How to count rows after a specific row?

Question

I have a contact mechanism between my website's users (always between two users). User A (sender) can send a message to users B (receiver) based on two rules:

1. User A could send 2 messages at most to user B and he must receive at least one message from user B to be able to send another 2.

2. User A could send 4 messages at most to everybody in daily period, not more.

And here is my table structure:

-- contact
+----+-----------+------------+--------------------------+------------+
| id | sender_id | receive_id | message | date_time |
+----+-----------+------------+--------------------------+------------+
| 1 | 123 | 456 | Hi, how are you? | 1492431111 |
| 2 | 123 | 789 | How are you doing? | 1492431112 |
| 3 | 456 | 789 | Why would you say that? | 1492431113 |
| 4 | 123 | 456 | Why don't you answer? | 1492431114 |
| 5 | 789 | 456 | Because the sky is high | 1492431115 |
| 6 | 123 | 789 | Hello? | 1492431116 | 
+----+-----------+------------+--------------------------+------------+

And here is my current query:

INSERT INTO contact(sender_id, receive_id, message, date_time )
SELECT ?, ?, ?, unix_timestamp()
FROM dual
WHERE NOT EXISTS(
 SELECT count(*) AS num_day,
 FROM contact
 WHERE user_id = ?
 AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))
 HAVING num_day > 4
) 

As you can see, only the second rule is implemented in my query. How can I also implement the first rule to the query?

• Solarflare: you can use where date_time > (select max(date_time) from .... But on a general note: it would be very careless if you would simply silently not insert the messages without a warning. So check that before you insert, and display an error.
• Andriy M: Or, to put it differently, no more than two in a row to the same receiver and no more than four in one day to the same receiver, correct?
• stack: @AndriyM Nope .. no more than two row to the same receiver (without any reply) and no more than four in one day to everyone (not the same receiver)
• Rick James: I wish tweets had those rules!

Answer 1

I always find views and subqueries easier to read and develop mainly as you can test and edit one section at a time. Depending on the number of messages and your system it may be a little slower though I doubt it. In this case I am going to use a union query.

I would also check first and if the limits are not exceeded then perform the insert (unless you doing millions of messages). Create a view of the total number of sent messages

 CREATE view vwMessagesSent AS
 SELECT *
 FROM `contact`
 WHERE date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))

This reduces the overall complexity of

SELECT Least(4,4 - count(id)) as sendLimit
FROM `vwMessagesSent`
WHERE user_id = ?
UNION ALL
SELECT Least(2,
 IF(
 (SELECT COUNT(id) FROM `vwMessagesSent` WHERE receiver_id = ? AND user_id = ?)=1, 4, 2)
 ) - (SELECT COUNT(id) FROM `vwMessagesSent` WHERE user_id = ? AND receiver_id = ?)
) 
FROM `vwMessagesSent`
WHERE user_id = ? AND receiver_id = ?
order by SendLimit
LIMIT 1

Note: Not tested - should be close to what you are after

We are using a few interesting SQL items out of the box. Least takes the lowest number of a collection of numbers. It allows us to only return the number of messages the user is allowed to send and for you to warn the user that the insert/message send was not going to be undertaken as they are over quota.

UNION allows you to merge two queries and we use the ALL to make sure we have multiple rows (rather than using the distinct key

LIMIT 1 We sort the order of the two rows returned with the minimum number available to be sent and then only return that row.

Hope this helps. Happy to clarify or fix the main SQL and I would be looking at using at a stored procedure to improve the logic even further.