MySQL按发送/接收的最后一条消息获取联系人的有序列表 [英] MySQL get ordered list of contacts by last message sent/received

问题描述

这是我的情况，我有2个表格，一个关于朋友，另一个关于消息. 朋友表是这样的:

this is my situation,I have 2 tables, one about friends, another about messages. Friends table is like this:

user_id|friend_id|accepted
  12   |   1      |   1
  13   |   1      |   1
   1   |   3      |   1

接受的值可以是0或1.(1接受，0不可以)

accepted can be 0 or 1. (1 accepted, 0 nope)

邮件表

message|time    |user_id|receiver_id
  hi!  | 1328688|   1   |   12
  hey  | 1343409|   12  |   1

时间是一个时间戳，所以我需要按顺序列出 每个朋友的最高时间戳.

Time is a timestamp, so i need to list in order by the highest timestamp for each friend.

我需要按最后一条消息(发送/接收)的顺序列出所有联系人(已接受= 1). 换句话说，我需要按发送者和接收者分组，并且从每个朋友中只取一个值(最后一个). 用户"12"可以在user_id或friend_id中，并且在user_id和Receiver_id中...所以我需要同时检查两者.

I need to list all contacts (that are accepted = 1) in order of last message (send/received). In other words, i need to group by sender and receiver, and take only one value (the last) from each friend. The user "12" can be in user_id or friend_id, and in user_id and receiver_id...so i need to check both.

有人可以帮助我吗?谢谢盖兹！

Someone can help me? Thanks guyz!

编辑 我的查询:

SELECT * FROM friends,messages
                         WHERE  (friends.user_id='$my_id'
                         OR  friends.friend_id ='$my_id'
                         AND friends.accepted='1')
                         AND messages.user_id='$my_id'
                         OR messages.receiver_id='$my_id'

                         GROUP BY  friends.friend_id
                         ORDER BY messages.time DESC");

我尝试了INNER JOIN，但这是个疯狂的小伙子(我对mysql不太满意)

I tried an INNER JOIN, but it's crazy guyz (i'm not so able with mysql)

编辑2

消息表

朋友表

编辑3 $ my_id对于获取我的user_id是可变的

EDIT 3 $my_id is variable for take my user_id

推荐答案

已解决，此查询对我有用

SELECT messages.* FROM messages, (SELECT MAX(id) as lastid
                   FROM messages
                   WHERE (messages.receiver_id = '$myid' OR messages.sender_id = '$myid')

                   GROUP BY CONCAT(LEAST(messages.receiver_id,messages.sender_id),'.',
                   GREATEST(messages.receiver_id, messages.sender_id))) as conversations
                   WHERE id = conversations.lastid
                   ORDER BY messages.time DESC

这篇关于MySQL按发送/接收的最后一条消息获取联系人的有序列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！