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

Question

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!

Answer 1

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 .