私人消息传递系统.列出每个对话的最后一条消息 [英] Private messaging system. Listing last message of each conversation

问题描述

可以说这是数据库结构:

Lets say this is the database structure:

SELECT * FROM `pms` where id_to = 1 or id_from = 1

这将返回他已接收或发送的所有消息，

This would return all the messages that he has recived or sent,

那么我该如何从用户1的每次对话中检索到最后一条消息?

So how can I retrieve the last message from each conversation that the user 1 may have?

PD:当两个用户之间有一条或多条消息时，我称其为对话

PD: I call it conversation when there is one or more messages between two users

-编辑-

因此，鉴于此数据库内容:

So given this database content:

我们要获取ID 4和6

We want to get id 4 and 6

推荐答案

这假定id是一个自动增量列:

This assumes id is an auto-increment column:

SELECT MAX(id) AS id
FROM pms
WHERE id_to = 1 OR id_from = 1
GROUP BY (IF(id_to = 1, id_from, id_to))

假设您已经索引了id_from和id_to，则此变体最有可能会表现更好，因为MySQL不知道如何处理OR:

Assuming you have id_from and id_to indexed, this variation will most likely perform better because MySQL doesn't know what to do with an OR:

SELECT MAX(id) AS id FROM
(SELECT id, id_from AS id_with
FROM pms
WHERE id_to = 1
UNION ALL
SELECT id, id_to AS id_with
FROM pms
WHERE id_from = 1) t
GROUP BY id_with

以下是获取有关这些ID的消息的方法:

Here's how to get the messages for those ids:

SELECT * FROM pms WHERE id IN
    (SELECT MAX(id) AS id FROM
    (SELECT id, id_from AS id_with
    FROM pms
    WHERE id_to = 1
    UNION ALL
    SELECT id, id_to AS id_with
    FROM pms
    WHERE id_from = 1) t
    GROUP BY id_with)

这篇关于私人消息传递系统.列出每个对话的最后一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！