MySQL查询以列出我发过消息或收到过消息的 friend

Question

这是我的两个表:

TABLE: friends

'id' // primary id, auto increment
'uid' // user id who sent friend request
'fid' // friend id who received friend request
'status' //status of friendship, 1=active

.

TABLE: messages

'id' // message id (primary id), auto increment
'uid' // user who sent the message
'fid' // friend who received the message
'time' // time when the message was sent
'status' // status of message, read or unread.

我只想显示在上次发送消息时(由 friend 或我发送)我已向其发送消息或从其接收消息的 friend 列表。一个 friend 应该只列出一次。我应该怎么做？

Answer 1

I want to show only the list of friends I have sent messages to or received messages from ordered by the time of last message sent (by the friend or by me).

试试这个:

SELECT DISTINCT friends.id
FROM messages m
INNER JOIN
(
    SELECT uid id FROM friends WHERE status = 1 AND fid = myuserid
    UNION ALL
    SELECT fid FROM friends WHERE status = 1 AND uid = myuserid
) friends ON m.fid = friends.id OR m.uid = friends.id

但是，如果有一个users表，你可以这样做:

SELECT 
  senders.name 'From', 
  recievers.name 'To', 
  m.id, 
  m.body, 
  m.messagetime,
  m.status
FROM messages m
INNER JOIN
(
    SELECT uid id FROM friends WHERE status = 1 AND fid = 1
    UNION ALL
    SELECT fid    FROM friends WHERE status = 1 AND uid = 1
) friends ON m.fid = friends.id OR m.uid = friends.id
INNER JOIN users senders ON m.uid = senders.id
INNER JOIN users recievers ON m.fid = recievers.id
WHERE m.uid = 1 
   OR m.fid = 1
ORDER BY m.messagetime ASC OR DESC

SQL Fiddle Demo

例如，这会给你:

| FROM | TO | ID |   BODY | MESSAGETIME | STATUS |
--------------------------------------------------
|   Me |  B |  1 |  hiiii |  2012-12-01 |      1 |
|    c | Me |  7 | sadfds |  2012-12-01 |      1 |
|   Me |  B |  8 |    ddd |  2012-12-10 |      1 |

---

这个查询是如何工作的？

查询:

SELECT uid id FROM friends WHERE status = 1 AND fid = myuserid
UNION ALL
SELECT fid    FROM friends WHERE status = 1 AND uid = myuserid

会给你你的 friend 列表，你的 friend 是:

• 用户向您发送了好友请求并被接受，或者
• 一个用户，你向他发送了一个好友请求，他接受了。

这就是为什么我使用 UNION ALL 和 fid = 你的用户 ID，我还假设 status = 1 意味着友谊请求被接受。

这些是你的 friend 。然后，要获取您已向其发送消息或从其接收消息的 friend 的列表，我们必须将此结果集与 messages 表连接起来。但是要获取发送给您的消息或您发送的消息，我们必须选择加入条件 m.fid = friends.id OR m.uid = friends.id。就是这样。