我正在开发一个带有两个表和另一个包含用户信息的表的消息传递系统。
对话可以是 2 个或更多用户之间的对话。每个对话都有一个 UID,用户之间交换的每条消息都标有该对话 UID。
以下是表格:
conversation_list
:此表中的每一行链接user_id
和conversation_id
,它还包含用户上次查看对话的时间。
`id` -> unique ID, autoincremented
`user_id` -> This contains the user associated with the conversation.
`conversation_id` -> This contains the UID of the conversation
`date_lastView` -> This field has the time that the user viewed the conversation last
conversation_messages
:此表中的每一行都包含一条消息
`id` -> unique ID, autoincremented
`user_id` -> This contains the user that sent the message.
`conversation_id` -> This contains the UID of the conversation
`date_created` -> This contains the time when the message was posted
`message` -> This contains the message
users
:此表中的每一行都包含一个用户
`User_ID` -> UID of the user
`FirstName` -> This contains the first name of the user
`LastName` -> This contains the last name of the user
我已经有一个SQL查询来获取每个对话的最后一条消息。在这里:
SELECT *
FROM conversation_messages AS m
JOIN
(SELECT mx.conversation_id,
MAX(mx.date_created) AS MaxTime
FROM conversation_messages AS mx
GROUP BY mx.conversation_id) AS mx ON m.conversation_id = mx.conversation_id
AND m.date_created = mx.MaxTime
JOIN
(SELECT mu.conversation_id
FROM conversation_list AS mu
WHERE mu.user_id = :USER_ID_CONNECTED
GROUP BY mu.conversation_id) AS mux ON m.conversation_id = mux.conversation_id
JOIN conversation_list AS mu ON m.conversation_id = mu.conversation_id
GROUP BY mu.conversation_id
ORDER BY m.date_created DESC
我现在想在这个完美工作的查询中添加返回的功能:
- 每个对话的未读消息数(
date_creaded
大于登录用户的date_lastView
的所有消息数) - 一个数组,其中包含每个会话中每个用户的
User_ID
,并按他们上次在对话中发布消息的时间排序。 - 与最后一个数组的想法相同,但具有用户的
FirstName
和LastName
。
我尝试了一些事情,但我真的没有成功,所以我现在向SO社区寻求宝贵的帮助。
所有这些只能显示登录用户参与的对话。
它有帮助,我创建了一个SQLFiddle
用户对话中的未读消息数(此处为用户 #6):
SELECT l.conversation_id, count(*)
FROM conversation_list l
JOIN conversation_messages m ON m.conversation_id = l.conversation_id AND m.date_created > l.date_lastview
WHERE l.user_id = 6
GROUP BY l.conversation_id
按上次活动排序的对话参与者:
SELECT conversation_id, user_id, max(date_created) as last_active
FROM conversation_messages
GROUP BY conversation_id, user_id
ORDER BY conversation_id, last_active
第三个查询应该和第二个查询一样,只是在user_id
上再连接一个表,对吧?
我对查询添加了 3 项改进来获取未读消息:
- 如果last_view字段为 null,它将假定该用户从不检查其消息,因此所有消息都将是"未读" 的
- 仅将不是由用户创建的邮件标识为新邮件。
- 当没有未读邮件时,计数将返回 0
SELECT l.conversation_id, count(m.id)
FROM conversation_list l
LEFT JOIN conversation_messages m ON m.conversation_id = l.conversation_id AND (l.date_lastview IS NULL OR m.date_created > l.date_lastview) AND m.user_id != 6
WHERE l.user_id = 6
GROUP BY l.conversation_id