Query a database to retrieve the last message from each user (e.g. facebook message)

Question

I need to get the last message between a registered user and any user that he has ever talked to, just like facebook. it will look like facebook.com/messages. Here is what I have done so far

Database

user_table
- cwid
- first_name
- last_name
- user_image_link

message
- id
- cwid1 (sender ID)
- cwid2 (receiver identifier)
- message (message content)
- messagetime (timestamp)
- userread (enum 'Y' 'N') `

Request Registered user has id = 1

SELECT user_table.cwid, message.cwid1, message.cwid2, message.message, message.userread, MAX(message.messagetime), user_table.first_name, user_table.last_name, user_table.user_image_link FROM message INNER JOIN user_table ON message.cwid1 = user_table.cwid WHERE (cwid2="1") GROUP BY cwid1 ORDER BY messagetime DESC 

This returns the last messages the user received from all the people who sent him a message. I also need to receive all messages that were sent by this user in a similar way, this can be done using

 SELECT user_table.cwid, message.cwid1, message.cwid2, message.message, message.userread, MAX(message.messagetime), user_table.first_name, user_table.last_name, user_table.user_image_link FROM message INNER JOIN user_table ON message.cwid1 = user_table.cwid WHERE (cwid1="1") GROUP BY cwid2 ORDER BY messagetime DESC 

I need them both to be mixed with individual users and sorted by message, just like Facebook posts. I am new to MySql, any help is much appreciated. Thanks!

Answer 1

I worked on this a bit more and made this request. - It displays messages from the latest users who sent me a message or received a message from me - It also includes messages from the group chat, groups can be created by anyone, and anyone inside the group can invite their friends - It also includes a read flag / unread to highlight unread messages in the frontend.

This is not the most efficient or elegant request, but it works. Any improvements are very welcome.

 SELECT * FROM ( SELECT * FROM ( SELECT * FROM ( SELECT users.id,'0' AS groupId, users.name, users.profilePic, messages.time,messages.message , 'Y' AS unread FROM users INNER JOIN messages ON messages.userId2 = users.id WHERE messages.userId1 = '" . $userId . "' UNION SELECT users.id,'0' AS groupId, users.name,users.profilePic, messages.time, messages.message , messages.read AS unread FROM users INNER JOIN messages ON messages.userId1 = users.id WHERE messages.userId2 = '" . $userId . "' ) AS allUsers ORDER BY TIME DESC ) AS allUsersSorted GROUP BY id UNION select * from( SELECT '0' AS id, msgGroups.id AS groupId, msgGroups.name, msgGroups.image AS profilePic, IF(userGroupMsg.time IS NULL,userGroups.createdOn,userGroupMsg.time ) AS time,IF(userGroupMsg.message IS NULL,'',userGroupMsg.message ), 'Y' AS unread FROM msgGroups LEFT JOIN userGroups ON msgGroups.id = userGroups.groupId LEFT JOIN userGroupMsg ON msgGroups.id = userGroupMsg.groupId WHERE userGroups.userId = '" . $userId . "' ORDER BY time DESC )AS allUsersSorted GROUP BY groupId )AS allSorted ORDER BY TIME DESC 

Please improve if anyone can.

Answer 2

Maybe something like this:

 SELECT DISTINCT m.id, m.cwid1, m.cwid2, m.message, m.userread, m.messagetime, u.first_name, u.last_name, u.user_image_link FROM user_table u, message m, ( SELECT DISTINCT IF(cwid1 = "1", cwid2, cwid1) AS id FROM message WHERE "1" IN (cwid1, cwid2) ) partner WHERE u.cwid = partner.id AND (m.messagetime, m.cwid1, m.cwid2) IN ( SELECT MAX(messagetime), cwid1, cwid2 FROM message WHERE (cwid1, cwid2) IN ((partner.id,"1"),("1",partner.id)) GROUP BY cwid1, cwid2 ) ORDER BY m.messagetime DESC 

And note that all columns in the FROM that are not displayed in an aggregate function of type MAX must have in the GROUP BY . MySQL does not cause an error in this case, but the values ​​in non-aggregate columns are random.

Answer 3

I would see how Facebook sets up the FQL table for posts here: https://developers.facebook.com/docs/reference/fql/unified_message . And base your table architecture on this.

Answer 4

 Try this select m.* ,u.* from messages m inner join ( select max(id) as maxid from messages where messages.list_id = " '. $data['list_id'] .'" group By (if(sender_id > reciever_id, sender_id, reciever_id)), (if(sender_id > reciever_id, reciever_id, sender_id)) ) t1 on m.id=t1.maxid