How can I make a dynamic limit selecting?

Question

I have a table like this:

// notifications
+----+--------------+------+---------+------------+
| id |      event   | seen | id_user | time_stamp |
+----+--------------+------+---------+------------+
| 1  | vote         | 1    | 123     | 1464174617 |
| 2  | comment      | 1    | 456     | 1464174664 |
| 3  | vote         | 1    | 123     | 1464174725 |
| 4  | answer       | 1    | 123     | 1464174813 |
| 5  | comment      | NULL | 456     | 1464174928 |
| 6  | comment      | 1    | 123     | 1464175114 |
| 7  | vote         | NULL | 456     | 1464175317 |
| 8  | answer       | NULL | 123     | 1464175279 |
| 9  | vote         | NULL | 123     | 1464176618 |
+----+--------------+------+---------+------------+ 

I'm trying to create a recent inbox messages box. I want to fetch both read and unread message for each user based on this condition:

Note: Always there should be at least 2 messages in the end of list which are read (seen = 1) .

The result should be containing 15 rows by default. Unless:

• there is more that 13 unread message (seen = NULL) (plus two read messages that have been read and always there).
• all messages are less than 15 in total.

---

Here is some examples: (read message: seen = 1, unread message: seen = NULL)

1. • Info: read message: 1, unread message: 1
   • Output: 2 rows. (containing one read message and one unread message)

---

2. • Info: read message: 40, unread message: 1
   • Output: 15 rows. (containing 14 read messages and 1 unread message)

---

3. • Info: read message: 12, unread message: 20
   • Output: 22 rows. (containing 2 read messages and 20 unread message)

---

4. • Info: read message: 0, unread message: 16
   • Output: 16 rows. (containing 0 read messages and 16 unread message)

---

5. • Info: read message: 25, unread message: 15
   • Output: 17 rows. (containing 2 read messages and 15 unread message)

---

6. • Info: read message: 4, unread message: 8
   • Output: 12 rows. (containing 4 read messages and 8 unread message)

---

7. • Info: read message: 745, unread message: 4
   • Output: 15 rows. (containing 11 read messages and 4 unread message)

As you see, I'm trying to fetch all unread messages plus at least two read messages. Also the number of rows should be 15 by default. How can I do that?

Here is my query:

SELECT id, event seen, time_stamp 
  FROM notifications
 WHERE id_user = :id AND
    LIMIT 15 OR IF (seen IS NULL > 15) THEN seen AND 
                   SELECT id, event seen, time_stamp FROM notification
                     WHERE id_user = :id AND seen IS NOT NULL LIMIT 2
                ENDIF

Answer 1

One method comes close:

SELECT id, event, seen, time_stamp 
FROM notifications n
WHERE id_user = :id
ORDER BY (seen IS NULL) desc, time_stamp desc
LIMIT 15;

However, this does not get all unseen messages. So, here is sort of hack to do this:

(SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id AND seen IS NULL
) UNION 
(SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id 
 ORDER BY (seen IS NULL) desc, time_stamp desc
 LIMIT 15
)
ORDER BY (seen IS NULL) desc, time_stamp desc;

The first subquery gets all unseen messages. The second gets fifteen messages. The UNION removes duplicates, but no other limit is applied. I believe this does what you want.

Answer 2

There is no possibility to use varying values for LIMIT in MySQL.

The workaround would be to compute the value for the LIMIT and save it into a variable, and then use the prepared statements