php - SQL 显示未读消息数...包括未读回复

Question

我正在为 CMS 构建消息系统，但找不到获取消息链中未读消息数量的方法。

我需要一种方法来查明主要消息或其任何回复消息是否未读。

我有一个名为“messages”的表，其中包含以下字段:id、事件[1,0]、主题、消息、日期时间、user_from、user_to、回复、查看、删除

当阅读消息时，我将用户 ID 以逗号分隔存储在查看的字段中:,3,4,12,

然后，为了查明它是否被查看，我会像这样查找发件人的 ID:

m.viewed LIKE '%,$user_id,%'

我只是弄清楚如何将该逻辑放入此 SQL 选择中，以便我可以查看是否应将父消息标记为未读。以下 SQL 效果很好，可以获取所有消息和回复数量，并按日期时间对它们进行排序。

SELECT m.*, COUNT(*) AS num_replies, MAX(r.datetime) AS max_datetimeunread_replies
FROM directus_messages AS m
LEFT JOIN directus_messages as r
    ON m.id = r.reply
WHERE m.active = '1' 
AND m.removed NOT LIKE '%,$user_id,%' 
GROUP BY m.id
HAVING m.reply = '0' 
ORDER BY datetime DESC, max_datetime DESC

任何帮助将不胜感激......我无法理解它!

Answer 1

您应该查看这篇文章:Managing Hierarchical Data in MySQL

--编辑--更新

好的，您只有 1 级回复，因此不需要上述内容。

试试这个:

SELECT m.*
     , COUNT(*) AS num_replies
     , MAX(r.datetime) AS max_datetime
     , (m.viewed LIKE '%,$user_id,%') 
         AS message_viewed    --shows True or False
     , SUM(r.viewed NOT LIKE '%,$user_id,%')
         AS unread_replies    --shows number of unread replies
FROM directus_messages AS m
  LEFT JOIN directus_messages as r
    ON m.id = r.reply
WHERE m.active = '1' 
  AND m.removed NOT LIKE '%,$user_id,%' 
GROUP BY m.id
HAVING m.reply = '0' 
ORDER BY m.datetime DESC
       , max_datetime DESC
;