CODEWITHSUNDEEP
mysqlselect
Yako malin
Yako malin

Reputation: 3

Request to select the last 10 send/received messages to/by different users

I want to select the 10 last messages you received OR you sent TO different users.

For example the results must be shown like that: 

1. John1 - last message received 04/17/10 3:12 
2. Thomy - last message sent 04/16/10 1:26 
3. Pamela - last message received 04/12/10 3:51 
4. Freddy - last message received 03/28/10 9:00 
5. Jack - last message sent 03/20/10 4:53 
6. Tom - last message received 02/01/10 7:41 
.....

Table looks like: 

CREATE TABLE `messages` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`sender` int(11) DEFAULT NULL, 
`receiver` int(11) DEFAULT NULL, 
`content` text 
)

I think Facebook (and the iPhone) uses this solution. When you go to your mail box, you have the last messages received/sent grouped by Users (friends).

So I will take an example. If I have theses messages (THEY ARE ORDERED YET):

**Mike**
**Tom**
**Pam**
Mike
Mike
**John**
John
Pam
**Steve**
**Bobby**
Steve
Steve
Bobby

Only Message with **** should be returned because they are the LAST messages I sent/received By User.

In fact I want the last message of EACH discussion.

What is the solution?

Upvotes: 0

Views: 950

Answers (4)

otheroom
otheroom

Reputation: 171

Max Shawabkeh's query really helped me understand this issue. In implementing it on a site that I'm developing I found an edge condition that it missed.

There are essentially three states that are possible for each message thread:

  1. You have sent messages back and forth.
  2. You have only sent messages to the other user.
  3. You have only received messages from the other user.

("You" is defined as user 12345 for whom you are retrieving message threads.)

Max's query missed state #3. I found that adding a WHERE clause to the query in the INNER JOIN not only fixed that, it also limited the results to only those threads related to user 12345.

SELECT   m1.*
FROM     messages m1
         INNER JOIN (SELECT   MAX(time) AS time,
                              IF(receiver = 12345, sender, receiver ) AS user
                     FROM     messages
                     WHERE    sender_id = 12345 OR receiver_id = 12345
                     GROUP BY user) m2
           ON m1.time = m2.time AND (m1.sender = m2.user OR m1.receiver = m2.user)
WHERE    sender = 12345 OR receiver = 12345
ORDER BY m1.time DESC

Upvotes: 1

Dmitry Yudakov
Dmitry Yudakov

Reputation: 15744

Perhaps you're looking for GROUP BY, although having separate fields for Sender and Receiver makes it harder.

IMHO it would be better to have UserID and MessageDirection SET('IN','OUT' ).

Then you could make

SELECT UserID, MAX(time) GROUP BY UserID

Edit

If you want last 10 

SELECT UserID, MAX(time) GROUP BY UserID ORDER BY time LIMIT 10

It's probably not the fastest solution and can be optimized.

Upvotes: 0

Max Shawabkeh
Max Shawabkeh

Reputation: 38683

The question is somewhat unclear. If you simply want the last 10 messages that involve user #12345, simply use:

SELECT   *
FROM     messages
WHERE    sender = 12345 OR receiver = 12345
ORDER BY time DESC
LIMIT    10

Update to match your updated requirements. Not the most elegant, but works as long as no user sends two messages at the same second.

SELECT   m1.*
FROM     messages m1
         INNER JOIN (SELECT   MAX(time) AS time,
                              IF(receiver = 12345, sender, receiver ) AS user
                     FROM     messages
                     GROUP BY user) m2
           ON m1.time = m2.time AND (m1.sender = m2.user OR m1.receiver = m2.user)
WHERE    sender = 12345 OR receiver = 12345
ORDER BY m1.time DESC

Upvotes: 3

Yako malin
Yako malin

Reputation: 3

I think I have found a solution using Max Shawabkeh tips. But in fact I think I can optimize it.

SELECT *
FROM 

(
SELECT *
FROM messages
ORDER BY sendtime DESC
) messages2

WHERE receiver = @id_user
OR sender = @id_user
GROUP BY IF( receiver = @id_user, sender, receiver )

I think there is a better solution ....

Upvotes: 0

Related Questions