ExoSkeleton321 ExoSkeleton321 - 9 days ago 5
MySQL Question

Get last Message Reply From Conversation

so I have two tables

chats
and
chats_reply
. The structure is the following.

chats

--------------------------------------------------
| chat_id | user_one | user_two | created_at
--------------------------------------------------
| 1 | 1 | 2 | something here
--------------------------------------------------


chats_reply

-------------------------------------------------------------------------
| chatReply_id | chat_id | user_id | reply | created_at
-------------------------------------------------------------------------
| 1 | 1 | 1 | Message 1 | something here
-------------------------------------------------------------------------
| 2 | 1 | 2 | Message 2 | something here
-------------------------------------------------------------------------


I'm having a bit of a problem with my query. Let's say my user_id is 1. I want to return a list of all my chats with the last message that was sent. I already have the query that lists all my chats, but it doesn't return the last message it return the first message of the conversation. This is my query:

SELECT
chats.chat_id,
chats.created_at AS ChatTime,
chats_reply.reply,
chats_reply.created_at AS ReplyTime,
chats_reply.status,
users.name,
users.last_name,
users.email

FROM chats

INNER JOIN chats_reply
ON chats.chat_id = chats_reply.chat_id

INNER JOIN users
ON users.user_id =
CASE
WHEN chats.user_one = '1'
THEN chats.user_two
WHEN chats.user_two = '1'
THEN chats.user_one
END

WHERE chats.user_one = '1' OR chats.user_two = '1'

GROUP BY chats_reply.chat_id

ORDER BY chats_reply.chatReply_id DESC


This query returns everything I'd expect, the problem is it return
Message 1
from the
chats_reply
table when I want it to return
Message 2
. Any help is greatly appreciated.

Answer

Use WHERE for filtering. Not GROUP BY:

SELECT c.chat_id, c.created_at AS ChatTime,
       cr.reply, cr.created_at AS ReplyTime, cr.status,
       u.name, u.last_name, u.email
FROM chats c INNER JOIN
     chats_reply cr
     ON c.chat_id = cr.chat_id INNER JOIN
     users u
     ON (u.user_id = c.user_two AND c.user_one = 1) OR
        (u.user_id = c.user_one AND c.user_two = 1)
WHERE 1 IN (c.user_one, c.user_two) AND
      cr.chatReply_id = (SELECT MAX(cr2.chatReply_id)
                         FROM chat_reply cr2
                         WHERE cr2.chat_id = cr.chat_id
                        );