mysql - 有没有更简单的方法来实现这种用户消息传递方式？

Question

我为用户创建了一个消息系统，它允许他们向另一个用户发送消息。如果这是他们第一次说话，则开始新的对话，如果不是，则继续旧的对话。

用户收件箱列出了用户与所有其他用户进行的所有对话，然后按包含最新帖子的对话排序。

一个用户只能与另一个用户进行一次对话。

当用户点击其中一个对话时，他们会被带到一个页面，该页面会显示他们进行过的整个对话，最新的帖子位于顶部。所以它有点像消息聊天功能。

我有两张 table :

• 用户对话
• 用户留言

用户对话

包含一个自动增量 ID，即对话 ID，以及 userId 和friendId。

发起第一个对话的人将始终是 userId 和接收者friendId，然后对于该对话将永远不会改变。

+----+--------+----------+
| id | userId | friendId |
+----+--------+----------+

用户留言

包含特定消息，以及读取标志、时间和 session ID

+----+---------+--------+------+------+----------------+
| id | message | userId | read | time | conversationId |
+----+---------+--------+------+------+----------------+

工作原理

当一个用户去给另一个用户发消息时，将运行一个查询来检查两个用户在 userconversation 表中是否有匹配，如果有，则使用 conversationId 并继续对话，如果没有使用唯一的 conversationId 为他们创建一个新行。

复杂的地方

到目前为止一切都很好，但是在显示所有对话的消息收件箱时，按最新帖子排序，使用一个查询就变得很棘手..

为了能够列出对话，您必须首先找到每个对话的最新帖子，但由于您不能在组之前排序，因此不可能对两个表进行一次查询，因此我必须使用以下:

SELECT  
    c.id,
    c.userId,
    c.friendId,
    m2.message,
    m2.read,
    UNIX_TIMESTAMP(m2.time),      
    user1.username,
    user2.username  
FROM 
    (SELECT MAX(m1.id) AS MessageID 
     FROM usermessages m1 
     GROUP BY m1.conversationId) latest_msg

INNER JOIN usermessages m2 ON latest_msg.MessageID = m2.id 
INNER JOIN userconversation c ON m2.conversationId = c.id
INNER JOIN user user1 ON c.userId = user.id
INNER JOIN user user2 ON c.friendId = user.id

WHERE c.userId = :userId OR c.friendId = :userId
ORDER BY m2.id DESC
LIMIT 10

我只是不认为这是可以做到的最好方法，但我想不出其他方法也可以接近它？

数据库表是 InnoDB 以加快连接速度并提高数据完整性，因此我不能有两个自动增量行。

是否有另一种方法可以摆脱 userconversation 表并创建一个唯一的 Id 以放入 conversationId 列？然后我可以将 userId 和friendId 移到 usermessages ...但这会产生大量冗余数据？

Answer 1

嗯，也许我没有正确理解你的问题......但对我来说解决方案很简单:

SELECT c.*, MAX(m.time) as latest_post 
FROM conversations as c 
INNER JOIN messages as m ON c.id = m.conversation_id
WHERE c.userId = 222 OR c.friendId = 222 
GROUP BY c.id
ORDER BY latest_post DESC

这是我的测试数据:

对话:

id  userId  friendId
1   222     333
2   222     444

消息:

id  message     time (Desc)     conversation_id
14  rty     2012-05-14 19:59:55     2
13  cvb     2012-05-14 19:59:51     1
12  dfg     2012-05-14 19:59:46     2
11  ert     2012-05-14 19:59:42     1
1   foo     2012-05-14 19:22:57     2
2   bar     2012-05-14 19:22:57     2
3   foo     2012-05-14 19:14:13     1
8   wer     2012-05-13 19:59:37     2
9   sdf     2012-05-13 19:59:24     1
10  xcv     2012-05-11 19:59:32     2
4   bar     2012-05-10 19:58:06     1
6   zxc     2012-05-08 19:59:17     2
5   asd     2012-05-08 19:58:56     1
7   qwe     2012-05-04 19:59:20     1

查询结果:

id  userId  friendId    latest_post
2   222     444     2012-05-14 19:59:55
1   222     333     2012-05-14 19:59:51

如果不是这样......请忽略我的回答:P

希望对你有帮助