如何在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:

我正在尝试为特定用户选择至少15行。只有两个条件:

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

始终所有未读行(看到= NULL)应匹配,即使它们超过15行。
If the number of unread rows is more than 15, then it also should select 2 read rows (seen = 1).

如果未读行的数量超过15,那么它也应该选择2个读取行(看到= 1)。

Examples: read is the number of read rows and unread is the number of unread rows in notifications table.

示例:read是读取行数,unread是通知表中未读行数。

 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;

6 个解决方案

#1

Just select all unseen and(union with) 15 seen.

只要选择所有看不见的和(联合)15见。

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.

因此,您现在拥有所有未读和最多15个读取通知。

After that you are able to truncate (client-side) to 15 if there less than 15 unseen.

之后,如果少于15个看不见,你可以将(客户端)截断为15。

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:

一些伪代码php:

$read = $unread = 0;

while($row = $db->fetch()) {
  if ($row['seen']) $read++;
  if (!$row['seen']) $unread++;
  // ...
  if ($weHaveEnoughRows) break;
}

#2

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

#3

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:

我找到了解决方案。要添加第二个条件(如果有超过15个未读行,则选择两个读取行),我必须再使用一个UNION。像这样的东西:

(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.

第一个子查询获取所有看不见的行。第二个得到两行。第三行得到十五行。 UNION删除重复项,但不应用其他限制。

#4

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

SQL小提琴演示

#5

please try this one,

请试试这个,

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

表T通过time_stamp desc返回行号顺序的读取通知。

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

然后从T中选择行<= GREATEST(15-Count()未读,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

#6

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.

我可能会简化查询并在应用程序中使用一些后处理逻辑来处理有14或15行未读的边缘情况。只需选择最多17行而不是15行,当您在客户端应用程序中循环遍历结果集时,除非第14行和第15行未读,否则无需检索第16行和第17行。

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

#1