Pargat Dhillon Pargat Dhillon - 2 months ago 6
MySQL Question

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

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

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 .

Comments