Reputation: 6892
A conversation contains messages and one message can be send to 2 or more users.
The goal is to retrieve the last messages by user for each conversation, so where the messages in each conversation are the newest.
Also the message_user.time
is when the user has read the message so when it's equals to 0 it's means the message has not been read yet so it'll be even better if the first order will be the message_user where time is equal to 0 and the the message.time that are the biggest numbers.
I got those tables
Message User
user message time
-----|------------|-------
7 | 1 | 0
8 | 1 | 0
7 | 2 | 300
8 | 2 | 300
7 | 3 | 400
Message
id text conversation time
---|-------------|--------------|----------
1 | blah blah1 | 1 | 200
2 | blah blah2 | 1 | 300
3 | blah blah3 | 2 | 400
4 | blah blah4 | 2 | 500
The goal is to group the messages by conversation and then get the last message_user linked to that conversation that have the bigger timestamp first.
What i tried is this but i don't get the last messages (wrong order)
SELECT m.user, mu.message, mu.time, mu.id, m.text, m.time as message_time, m.conversation
FROM message_user as mu,message as m
WHERE mu.message=m.id AND mu.user=8
GROUP BY m.conversation
ORDER BY m.time DESC';
Then the next step will be: I don't know if it's possible but it if the order by can ouput the message_user where the time is equal to 0 first and then second order is message.time (i don't even know if it's possible in one request that will be perfect!)
For the user 8 the output should be this :
text conversation
-----------|------------
blah blah1 | 1 //because message_user.time = 0 (means message is unread)
blah blah3 | 2 //because message.time is the highest in the conversation
Thank you !
Upvotes: 3
Views: 1694
Reputation: 656
First, I'd suggest restructuring your tables to make this easier, but here's what I think you are asking for... Each conversation, showing only the last conversion message created, with the last time and user who read it (but prefer unread over this).
SELECT m.user as User_Sent, m.conversation, m.text, m.time as Time_Sent
, mu.id, mu.user as User_Read, mu.time as Time_Read
FROM message as m
JOIN (
SELECT mx.conversation, MAX(mx.time) as MaxTime
FROM message as mx GROUP BY mx.conversation
) as mx
On m.conversation = mx.conversation
And m.time = mx.MaxTime
JOIN (
SELECT mu.message, MAX(mu.time) as MaxTime, MIN(mu.time) as MinTime
From message_user as mu
GROUP BY mu.message
) as mux
On m.id = mux.message
JOIN message_user as mu
ON m.id = mu.message
AND CASE WHEN mux.MinTime = 0 THEN mux.MinTime ELSE mux.MaxTime END = mu.time
Upvotes: 4