在与其他用户的每次对话中查询最新消息

本文介绍了在与其他用户的每次对话中查询最新消息的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表messages用于用户之间的对话. 表格列的名称为:

I have a table messages for conversations between the users. The table columns' names are:

messageID | fromUser | forUser | message | submitDate | seen

样本数据:

1 | 1 | 2 | "hi" | "12341" | 0 2 | 2 | 1 | "hi" | "12342" | 0 3 | 1 | 3 | "hi" | "12343" | 0 4 | 1 | 4 | "hi 4" | "12344" | 0 5 | 2 | 1 | "hello" | "12345" | 0 6 | 1 | 2 | "hello how r u?" | "12346" | 0 7 | 3 | 1 | "hello user 1" | "12345" | 0

如何编写查询以查找本人与系统中其他用户之间发送的最后一条消息? 我的意思是最后一条消息是:

How I can write a query to find the last message that was sent between myself and every other user in the system? I mean last messages are:

between user 1 and 2 : "hello how r u?" between user 1 and 3 : "hello user 1" between user 4 and 1 : "hi 4""

我的查询:

$query = "SELECT DISTINCT `fromUser`, `forUser`, `message`, `seen`, `username`, `userPhoto` FROM `messages`,`user` WHERE (`forUser`= '$myUserID' OR `fromUser`= '$myUserID') AND (((`forUser`= `userID`) AND (`forUser` != '$myUserID')) OR ((`fromUser`= `userID`) AND (`fromUser` != '$myUserID'))) ORDER BY `submitDate` DESC";

但是此查询需要获取对话中的所有消息！我只需要最后一条消息.

but this query needs to fetch all messages in a conversation! I just need the last message.

推荐答案

如果messageId是auto_increment主键，则可以使用其值来区分每个对话中的最新消息.如果submitDate具有DATETIME或TIMESTAMP类型，则将是该目的的另一种选择，但是如果它具有DATE类型，则其分辨率是不够的.

If messageId is an auto_increment primary key then you can use its values to distinguish which is the latest message in each conversation. If submitDate has type DATETIME or TIMESTAMP then would be another choice for that purpose, but if it has type DATE then its resolution is not sufficient.

但是，关键是要识别和过滤最新消息的时间戳或ID.您可以使用合适的聚合(子)查询在每个会话的基础上标识ID或时间戳，并通过内部联接执行过滤，如下所示:

The key thing, though, is to identify and filter on the timestamps or ids of the latest messages. You can identify the IDs or timestamps on a per-conversation basis with a suitable aggregate (sub)query, and perform the filtering via an inner join, like so:

SELECT m.* FROM messages m JOIN ( SELECT MAX(messageId), CASE WHEN fromUser = '$myUserId' THEN forUser WHEN forUser = '$myUserId' THEN fromUser END AS otherUser FROM messages GROUP BY CASE WHEN fromUser = '$myUserId' THEN forUser WHEN forUser = '$myUserId' THEN fromUser END HAVING otherUser IS NOT NULL ) other ON m.messageId = other.messageId