php - mysql 获取对话中的最后一条消息

Question

我当前正在创建一个私有(private)消息收件箱，并希望显示每个唯一用户之间发送或接收的最后一条消息。我目前有这个

SELECT
 Message.id, Message.from, Message.body, Message.isread, Message.timestamp,
 User.id, User.username,
FROM messages as Message 
 LEFT JOIN users as User ON (Message.from = User.id)
    WHERE Message.id IN
     (SELECT MAX(`id`) FROM `messages` 
        WHERE `to` = '.$this->currentUser["id"].'
         OR `from` = '.$this->currentUser["id"].'
    GROUP BY `from`,`to`)
 ORDER BY Message.id DESC;

但它同时获取最后发送和接收的消息，我只想要当前用户与任何其他用户以及与之对话的用户之间发送的最后一条消息。

 `Message`.`id` is auto incremented, indvidual message id.
 `Message`.`from` is the ID of the user the message is from.
 `Message`.`to` is the ID of the user the message us to.

谢谢

Answer 1

理解我的回答可能来得太晚了，但仍然如此。这为我解决了这个问题:

SELECT id, user_to, user_from, body, msg_time FROM chat
WHERE id IN (SELECT MAX(id) FROM chat WHERE user_to=:id OR user_from=:id GROUP BY user_to,user_from) ORDER BY msg_time DESC

:id 稍后会更改为 $id var，如下所示(如果您使用 PDO)-

        $stmt=$this->conn->prepare("SELECT id, user_to, user_from, body, msg_time FROM chat
            WHERE id IN (SELECT MAX(id) FROM chat WHERE user_to=:id OR user_from=:id GROUP BY user_to,user_from) ORDER BY msg_time DESC");
        $stmt->execute(array(":id"=>$id));
        $stmt=$stmt->fetchAll(PDO::FETCH_ASSOC);
        return $stmt;

希望这对那些仍在寻找的人有所帮助 - 我花了很长时间；)干杯。