CODEWITHSUNDEEP
mysqlsql
Rehan
Rehan

Reputation: 39

MySQL query to list friends I have messaged or received message from

Here are my two tables:

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.

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). One friend should be listed only once. How am I supposed to do that?

Upvotes: 4

Views: 1463

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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).

Try this:

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

However, if there is a users table, you can do this:

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

For instance, this will give you:

| 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 |

How this query works?

The query:

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

will give you the list of your friends, a friend of yours is either:

  • A user sent a friendship request to you and accepted, or
  • A user you send him a friendship request and he accept it.

Thats why I used a UNION ALL with fid = your user id, and I also assumed that status = 1 means that the friendship request is accepted.

These are your friends. Then, to get the list of friends you have sent messages to or received messages from, we have to join this result set with the messages table. But to get the messages sent to you or the messages sent by you, we have to choose the join condition m.fid = friends.id OR m.uid = friends.id. Thats it.

Upvotes: 4

Related Questions