CODEWITHSUNDEEP
mysql
Brian Moreno
Brian Moreno

Reputation: 1027

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.

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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
                        );

Upvotes: 2

Related Questions