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 .

Answer 2

Maybe something like that:

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 please note that all columns in the FROM clause that does not appear in an aggregate function like MAX have to appear in the GROUP BY clause. MySQL doesn't raise an error in that case, but the values in the non-aggregate columns are random.