从每个会话sql获取最后的消息

I have a sql table which include conversation between users. I need to retrieve the last message from every conversation in order to preview it.

我有一个sql表，其中包括用户间的对话。我需要从每次对话中检索最后一条信息，以便预览。

id | sender   | receiver     | message      | date
 1 |        1 |            2 | Hello        | 2015-12-08 20:00
 2 |        2 |            1 | Hey          | 2015-12-08 20:10
 3 |        2 |            1 | You there?   | 2015-12-08 21:00
 4 |        1 |            3 | Yes          | 2015-12-08 21:15
 5 |        4 |            1 | Hey buddy    | 2015-12-08 22:00

I know many similar question on site but i couldn't fix this.

我在网站上知道很多类似的问题，但我无法解决。

I tried this code but not working properly:

我尝试了这个代码，但是没有正常工作:

SELECT *
FROM   messages
WHERE  receiver = '{$id}'
GROUP BY sender
ORDER BY id DESC
LIMIT 10;

4 个解决方案

#1

Just to define what is a conversation in your table is a pain, i suppose a conversation is all the rows where

仅仅是定义你桌子上的对话是一种痛苦，我想对话是所有的行。

(sender=@senderId && receiver=@receiverId) || (sender=@receiverId && receiver=@senderId)

(sender=@senderId & receiver=@receiverId) || (sender=@receiverId & receiver=@senderId)

Group by this concept, i don't even want to think it

按这个概念分组，我甚至不想去想它

For me you are missing a concept, the "conversation"

对我来说，你错过了一个概念，“对话”

If you have a table conversation like this

如果你有这样的桌位对话

ConversationId |  Users1  |  User2

And Message like

等消息

Id | ConversationId | UserSendingId | Message | Date

Now you can Group by ConversationId and take the last message like

现在您可以按ConversationId进行分组，并获取最后一条消息

SELECT *  <-- avoid * better use all row names
FROM Message 
Where id in (
 select max(id) from message group by ConversationId
)

The representation of the conversation table is just a fast approach you can do a better solution with a relation from 1 to many of conversation and users in conversation to avoid modified conversation table when you want to have more than 2 users per conversation.

对话表的表示只是一种快速的方法，您可以使用从1到许多会话的关系和会话中的用户来实现更好的解决方案，以避免在每次会话中有两个以上的用户时修改会话表。

#2

I think if you want to identify a conversation for a particular user, you will need to select rows where they are either the sender or the receiver.

我认为如果您想为特定的用户识别对话，您将需要选择它们所在的行，它们要么是发送方，要么是接收方。

Then to get the most recent message from the conversation, you can group by whichever one of sender/receiver the current user is not, then select the maximum ID.

然后，要从对话中获取最新的消息，您可以按当前用户不在的发送方/接收方之一进行分组，然后选择最大ID。

SELECT * FROM messages
WHERE id IN (
    SELECT MAX(id) AS last_msg_id 
    FROM messages WHERE receiver = ? OR sender = ? 
    GROUP BY IF(sender = ?, receiver, sender)
)

I don't think this query will perform very well, though. I agree with the other answer that it would be easier to query for conversations if conversations were defined in your database.

不过，我认为这个查询不能很好地执行。我同意另一个答案，如果在您的数据库中定义会话，那么查询会话将更容易。

#3

E.g.:

例如:

SELECT x.* 
  FROM my_table x
  JOIN 
     ( SELECT LEAST(sender,receiver) user1
            , GREATEST(sender,receiver) user2
            , MAX(date) date 
         FROM my_table 
        GROUP 
           BY user1
            , user2
     ) y
    ON LEAST(sender,receiver) = user1
   AND GREATEST(sender,receiver) = user2
   AND y.date = x.date;

#4

SELECT m.id, m.added_date, m.message, u.username, u.image, m.from_id, m.to_id
FROM tbp_registration AS u 
LEFT JOIN tbp_chats AS m ON m.from_id = u.id 
WHERE m.id IN ( 
  SELECT MAX(id) 
  FROM tbp_chats 
  WHERE from_id = '$user_id' OR to_id = '$user_id' 
  GROUP BY LEAST(from_id, to_id), GREATEST(from_id, to_id)
)

#1