当不存在任何记录时，未读消息计数应返回非零

dw1jzc5e 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(253)

这是我的表格结构：

tblTickets
ticket_id | subject | creator_id | created_date | last_updated | category_id | ticket_status

tblMessages
msg_id | msg | ticket_id | replied_by | reply_time

tblReadBy   - PrimaryKey: (ticket_id,user_id)
ticket_id | user_id | last_read_time

我正在尝试列出当前登录用户的所有票证。也就是说，仅由他们创建的票证。
此外，我显示一个小的颜色指示，让用户知道是否有任何未读的消息。
因此，每当用户查看一张票据（通过从列表中单击），一条记录就会被插入/更新 tblReadBy 与 ticket_id 以及 user_id 与当前时间一起）
我对列出门票的查询有点像这样：

SELECT 
    t.`ticket_id`, 
    t.`subject`, 
    t.`user_id`, 
    t.`created_date`, 
    t.`last_updated`
    ( 
        SELECT COUNT(*) 
        FROM `tblReadBy` 
        WHERE `ticket_id` = t.`ticket_id` AND 
              `user_id` = 'XXX' AND 
              `last_read_time` < t.`last_updated` 
    ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC

在这里， XXX 是当前登录用户的用户id。如果中有行，则此查询可以正常工作 tblReadBy 当前登录的表 user_id 以及 ticket_id .
但如果没有这一行的话 user_id 以及 ticket_id ，则根据逻辑，它是一个“未读票据”，但查询将返回 0 为了 has_unread_msg 列。
我怎样才能解决这个案子？
注意：将有多个用户回复一个票证。比如实际用户、版主（不止一个）、管理员等。
谢谢您
解决方案：
到目前为止，我发现的是：

SELECT 
    t.`ticket_id`, 
    t.`subject`, 
    t.`user_id`, 
    t.`created_date`, 
    t.`last_updated`
    ( 
        SELECT 
               COALESCE(SUM(CASE WHEN `last_read_time` < t.`last_updated` THEN 1 ELSE 0 END), 1)
        FROM `tblReadBy` 
        WHERE `ticket_id` = t.`ticket_id` AND 
              `user_id` = 'XXX'                  
    ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC

我仍在尝试测试所有可能的案例。

mysql

来源：https://stackoverflow.com/questions/53249022/when-no-records-exists-should-return-non-zero-for-unread-messages-count

3条答案

按热度按时间

cgh8pdjw1#

这可能会起作用，因为它左键联接两个表以确定tblreadby中是否有任何记录，然后在子查询中使用结果。不幸的是我不能测试它。

SELECT 
t.`ticket_id`, 
t.`subject`, 
t.`user_id`, 
t.`created_date`, 
t.`last_updated`
( 
    SELECT 
        CASE WHEN r.`ticket_id` IS NULL THEN 1 
             ELSE COUNT(*) 
        END AS message_count
    FROM `tblReadBy` 
    WHERE `ticket_id` = t.`ticket_id` AND 
          `user_id` = 'XXX' AND 
          `last_read_time` < t.`last_updated` 
) AS `unread_msg_count`
FROM `tblTickets` AS t
LEFT JOIN `tblReadBy` AS r ON t.`ticket_id` = r.`ticket_id` AND
           r.`user_id` = 'XXX'
ORDER BY t.`last_updated` DESC

您可能需要添加r。 ticket_id 在外部查询的选择列表中，如下所示：

SELECT 
t.`ticket_id`, 
r.`ticket_id`,
t.`subject`, 
t.`user_id`, 
t.`created_date`, 
t.`last_updated`
( 
    SELECT 
        CASE WHEN r.`ticket_id` IS NULL THEN 1 
             ELSE COUNT(*) 
        END AS message_count
    FROM `tblReadBy` 
    WHERE `ticket_id` = t.`ticket_id` AND 
          `user_id` = 'XXX' AND 
          `last_read_time` < t.`last_updated` 
) AS `unread_msg_count`
FROM `tblTickets` AS t
LEFT JOIN `tblReadBy` AS r ON t.`ticket_id` = r.`ticket_id` AND
           r.`user_id` = 'XXX'
ORDER BY t.`last_updated` DESC

赞(0）回复(0）举报 2021-06-18

aij0ehis2#

在我看来，您需要计算tblreadby中已更新的消息和从未读取过的新消息，即它们存在于tbltickets中，但不存在于tblreadby中。
这是我的解决方案，有点复杂，我很想看到有人改进它，但基本上我加入agains两个'选择。。。计数（*）´, 一个用于全新消息，另一个用于更新消息

SELECT t.ticket_id, subject, t.user_id, created_date, last_updated, IFNULL(c1.count, 0) + IFNULL(c2.count, 0)
FROM tblTickets t
LEFT JOIN (SELECT ticket_id, user_id, COUNT(*) count
           FROM tblTickets t
           WHERE NOT EXISTS (SELECT * 
                             FROM tblReadBy r   
                             WHERE t.ticket_id = r.ticket_id 
                             AND t.user_id = r.user_id)
           GROUP BY ticket_id, user_id) AS c1 ON c1.ticket_id = t.ticket_id AND c1.user_id = t.user_id
LEFT JOIN (SELECT r.ticket_id, r.user_id, COUNT(*) count
           FROM tblReadBy r
           JOIN tblTickets t ON t.ticket_id = r.ticket_id AND t.user_id = r.user_id
           WHERE r.last_read_time < t.last_updated
           GROUP BY r.ticket_id, r.user_id) AS c2 ON c2.ticket_id = t.ticket_id AND c2.user_id = t.user_id
WHERE t.user_id = 'aaa'

赞(0）回复(0）举报 2021-06-18

qq24tv8q3#

我们可以用 SUM() 函数的 NULL 处理行为。它会回来的 NULL 如果不存在行。
如果返回集没有行，sum（）将返回null
mysql自动将布尔比较运算符的结果类型化为0/1。利用这种行为，我们可以 Sum() 以及 Coalesce() .

SELECT 
    t.`ticket_id`, 
    t.`subject`, 
    t.`user_id`, 
    t.`created_date`, 
    t.`last_updated`
    ( 
      -- coalesce handles case when no rows
      SELECT COALESCE(SUM(`ticket_id` > 0), 1)
      FROM `tblReadBy` 
      WHERE `ticket_id` = t.`ticket_id` AND 
            `user_id` = 'XXX' AND 
            `last_read_time` < t.`last_updated` 
    ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC

赞(0）回复(0）举报 2021-06-18

我来回答

当不存在任何记录时，未读消息计数应返回非零

3条答案

相关问题

热门标签

最新问答