CODEWITHSUNDEEP
mysqlsqlsql-limit
Martin AJ
Martin AJ

Reputation: 6697

How to make a dynamic limit in MySQL?

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:

  1. Always all unread rows (seen = NULL) should be matched, even if they are more than 15 rows.

  2. 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;

Upvotes: 7

Views: 6752

Answers (6)

Tin Tran
Tin Tran

Reputation: 6202

please try this one,

table T returns read notifications with row number order by time_stamp desc.

You then select from T where row <= GREATEST(15-Count() of unread,2).

and then union all with unread

SELECT id,event,seen,time_stamp 
FROM 
  (SELECT id, event, seen, time_stamp,@row:=@row+1 as row 
   FROM notifications n,(SELECT @row := 0)r
   WHERE id_user = :id AND seen IS NOT NULL
   ORDER BY time_stamp desc
   )T
WHERE T.row <= GREATEST(15-
                   (SELECT COUNT(*) FROM notifications n
                    WHERE id_user = :id AND seen IS NULL),2)
UNION ALL
(SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id
 AND seen is NULL
)
ORDER BY (seen IS NULL) desc,time_stamp desc

Upvotes: 1

wchiquito
wchiquito

Reputation: 16551

Try:

SET @`id_user` := 123;

SELECT `id`, `event`, `seen`, `time_stamp`
FROM (SELECT `id`, `event`, `seen`, `time_stamp`, @`unread` := @`unread` + 1
      FROM `notifications`, (SELECT @`unread` := 0) `unr`
      WHERE `id_user` = @`id_user` AND `seen` IS NULL
      UNION ALL
      SELECT `id`, `event`, `seen`, `time_stamp`, @`read` := @`read` + 1
      FROM `notifications`, (SELECT @`read` := 0) `r`
      WHERE `id_user` = @`id_user` AND `seen` IS NOT NULL
            AND (
                 @`read` < (15 - @`unread`) OR
                 ((15 - @`unread`) < 0 AND @`read` < 2)
            )
) `source`;

SQL Fiddle demo

Upvotes: 1

Martin AJ
Martin AJ

Reputation: 6697

I find a solution. To add second condition (selecting two read rows if there is more than 15 unread rows), I have to use one more UNION. Something like 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 notification n
 WHERE id_user = :id AND seen IS NOT NULL
 LIMIT 2
)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 rows. The second gets two seen rows. The third gets fifteen rows. The UNION removes duplicates, but no other limit is applied.

Upvotes: 1

msheikh25
msheikh25

Reputation: 578

SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = 123 AND seen IS NULL

UNION

(SELECT id, event, seen, time_stamp 
FROM ( 
 SELECT id, event, seen, n.id_user, time_stamp, un.CNT
    FROM notifications n
    JOIN (
        SELECT COUNT(1) CNT, id_user
        FROM notifications
        WHERE id_user = 123 and seen is NULL
        group by id_user
        ) un
    ON n.id_user = un.id_user
    WHERE CNT > 15
) t1
WHERE t1.SEEN is not NULL
LIMIT 2)

UNION

SELECT id, event, seen, time_stamp 
FROM ( 
 SELECT id, event, seen, n.id_user, time_stamp, un.CNT
    FROM notifications n
    JOIN (
        SELECT COUNT(1) CNT, id_user
        FROM notifications
        WHERE id_user = 123 and seen is NULL
        group by id_user
        ) un
    ON n.id_user = un.id_user
    WHERE CNT < 15
) t1
WHERE t1.SEEN is not NULL

Upvotes: 1

vp_arth
vp_arth

Reputation: 14982

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;
}

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

I would perhaps simplify the query and use some post-processing logic in the application to handle the edge case around having 14 or 15 rows that are unread. Just select up to 17 rows instead of 15 and, as you loop through the result set in your client application, simply don't bother retrieving rows 16 and 17 unless rows 14 and or 15 are unread.

That query could be as simple as:

SELECT id, event, seen, time_stamp 
FROM notifications n
WHERE id_user = :id
ORDER BY seen DESC, time_stamp DESC
LIMIT 17

Upvotes: 0

Related Questions