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 select at least 15 rows for specific user. Just there is two conditions:
Always all unread rows (seen = NULL
) should be matched, even if they are more than 15 rows.
If the number of unread rows is more than 15, then it also should select 2 read rows (seen = 1
).
Examples: read
is the number of read rows and unread
is the number of unread rows in notifications
table.
read | unread | output should be
------|--------|-------------------------------------
3 | 8 | 11 rows
12 | 5 | 15 rows (5 unread, 10 read)
20 | 30 | 32 rows (30 unread, 2 read)
10 | 0 | 10 rows (0 unread, 10 read)
10 | 1 | 11 rows (1 unread, 10 read)
10 | 6 | 15 rows (6 unread, 9 read)
100 | 3 | 15 rows (3 unread, 12 read)
3 | 100 | 102 rows (100 unread, 2 read)
Here is my current query, it doesn't support second condition.
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;
Dynamic SQL is a programming technique you can use to build SQL statements as textual strings and execute them later. This technique could prove to be useful in some cases and therefore it's good to know we have it as an option. In today's article, we'll show how to create and execute dynamic SQL statements.
To get only the specified rows from the table, MySQL uses the LIMIT clause, whereas SQL uses the TOP clause, and Oracle uses the ROWNUM clause with the SELECT statement.
Just select all unseen and(union with) 15 seen.
SELECT id, event, seen, time_stamp
FROM notifications n
WHERE id_user = :id AND seen IS NULL
UNION ALL
(SELECT id, event, seen, time_stamp
FROM notifications n
WHERE id_user = :id AND seen IS NOT NULL
LIMIT 15)
So, you now have all unread and up to 15 read notifications.
After that you are able to truncate (client-side) to 15 if there less than 15 unseen.
Best place for do it, I think, is fetch loop.
Just count seen/unseen and break the loop at point you reach enough rows.
Some pseudocode php:
$read = $unread = 0;
while($row = $db->fetch()) {
if ($row['seen']) $read++;
if (!$row['seen']) $unread++;
// ...
if ($weHaveEnoughRows) break;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With