TRicks TRicks - 7 months ago 27
SQL Question

MySQL join 2 tables and retrieve the latest timestamp and message

I'm trying to recreate a conversation list like whatsapp where it displays the contact name of the person you are having a conversation with and also the time the message was posted and also the last message regardless of if it was from you or the contact that posted the message, In other words I need to always see the contact name and the last time the message was posted and the last message which could have been sent by me or the recipient, I also need to bring back the image of the other user from the users table.

USERS TABLE

userid | first_name | url |
---------------------------------------------
101 | name1 | www.image_url1.jpg |
102 | name2 | www.image_url2.jpg |
103 | name3 | www.image_url3.jpg |
104 | name4 | www.image_url4.jpg |


MESSAGES TABLE

MessageId | userid | senderid | message | timestamp |
-----------------------------------------------------------
1 | 101 | 102 | message1 | 1234567 |
2 | 102 | 101 | message2 | 1234578 |
3 | 101 | 102 | message3 | 1235679 |
4 | 104 | 101 | message4 | 1256379 |


What i'm trying to query from the database if my userid = 101 is the last message and timestamp from either me or the contact along with the contacts userid, first_name and url

userid | first_name | url | message | timestamp |
---------------------------------------------------------------------
102 | name2 | www.image_url2.jpg | message3 | 1235679 |
104 | name4 | www.image_url4.jpg | message4 | 1227878 |


1) I want to extract the userid, first_name and url of the contact from the users table

2) And extract the newest message and timestamp from the messages table from the contact

So in other words i need to show the last message and timestamp posted by either me or the other contact from the messages table and the url and username from the users table but not my username and image url just the contacts.

Here is the query i have so far

SELECT DISTINCT users.userid, users.first_name, users.url, message, MAX(messages.timestamp) AS utime FROM messages JOIN users ON users.userid = messages.senderid WHERE users.userid <> '101' AND (messages.userid = '101' OR messages.senderid = '101') GROUP BY 1,2,3 ORDER BY utime DESC


i also tried the below which didn't quite work

SELECT DISTINCT mems.userid, mems.first_name, mems.url, message, MAX(messaging.timestamp) AS utime
FROM messaging
JOIN mems
ON mems.userid = CASE
WHEN
messaging.senderid = '101'
THEN
messaging.userid
ELSE
messaging.senderid
END
WHERE mems.userid <> '101' AND (messaging.userid = '101' OR messaging.senderid = '101')
GROUP BY 1,2,3 ORDER BY utime DESC

Answer

Something like this should work.

SELECT IF(m.userid = '101', m.senderid, m.userid) contact, m.message, m.timestamp, u.first_name, u.url
FROM messages m
INNER JOIN(
    SELECT MAX(timestamp) max_time, IF(userid = '101', senderid, userid) contact 
    FROM messages 
    WHERE userid = '101' OR senderid = '101'
    GROUP BY contact
) j ON j.contact = IF(m.userid = '101', m.senderid, m.userid) AND m.timestamp = j.max_time
LEFT JOIN users u ON u.userid = IF(m.userid = '101', m.senderid, m.userid)

P.S. this doesn't actually tell you who sent the latest messages (you or your contact) it might be more useful to get both the senderid and userid for every result row, instead of just the contact id.