在与其他用户的每次对话中查询最新消息 [英] Query for the latest message in each conversation with every other user

问题描述

我有一个表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

这篇关于在与其他用户的每次对话中查询最新消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！