mysql - 通过加入获取未读计数

Question

我在使这个查询工作时遇到问题，所以基本思路是:

• 我有消息表，我想跟踪用户是否阅读了消息。注意:多个用户可能会收到同一条消息，所以简单地将已读列添加到消息中不是一种选择
• 每条消息都在一个线程中(有一个 thread_id 列)
• 我有另一个表user_read_message，它会在有人阅读消息时添加记录(user_id、message_id、read )

我想获取特定线程中用户的未读消息数。我正在按照这些思路尝试一些方法，但我无法让它工作:

SELECT m.thread_id, urm.user_id, urm.read 
FROM sup_messages as m 
    LEFT OUTER JOIN user_read_message as urm ON m.id = urm.message_id
WHERE m.thread_id = 76852 AND urm.user_id = 1337;

如果它起作用，会选择 thread_id 76852 中的所有消息，然后加入 user_read_message，其中 user_id 为 1337，他未阅读的消息将只是 null 。然后我会以某种方式计算读取为 0 或 NULL 的位置。

附言。如果有更好的想法如何建模，请告诉我!

Answer 1

我会这样做。将与 user_read_message 表相关的 WHERE 子句添加到该表的 JOIN 中。由于这是一个 LEFT JOIN，如果没有匹配项，从该表返回的所有字段都将为 NULL。将该表中的字段添加到始终填充的 WHERE 子句，然后检查它是否为 NULL。这意味着没有匹配项。

SELECT m.thread_id, 1337 AS user_id, COUNT(*) unread_messages 
FROM  sup_messages as m 
      LEFT OUTER JOIN user_read_message as urm 
      ON m.id = urm.message_id
      AND urm.user_id = 1337
WHERE m.thread_id = 76852 AND urm.message_id IS NULL;