Database query to fetch last message from every user (like facebook messages)

message, mysql, php

Solution

I worked on it a little bit more and made this query . - It lists messages from recent users , who sent me a message OR recieved a message from me - It also includes messages from Group Chat , groups can be created by anyone and anyone inside a group can invite their friends - It also includes read/unread flag to highlight unread messages at frontend .

This is not the most efficient or elegant query but it works. Any improvements are very much 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 .

Problem

I need to fetch last message between the logged in user and any user he ever chatted with , just like facebook. this will be like facebook.com/messages page. Here's what I have done till now The Database user_table - cwid - first_name - last_name - user_image_link message - id - cwid1 (sender id) - cwid2 (receiver id) - message (message content) - messagetime (timestamp) - userread (enum 'Y' 'N')` The Query Logged in 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 last messages received by a user from all the people who have sent him a message .I need to also fetch all the messages which have been sent by this user similarly , it can be done by ``` 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 mixed with distinct users and sorted by messagetime just like Facebook Messages .I am a newbie to MySql, any help is greatly appreciated. Thanks!

Original source