Filter out the latest message of user whether it is in sender or

Filter out the latest message of user whether it is in sender or reciver by item id

i tired group by with item id by using max with id but not able to filter out the single row only for each item id in table with latest message whether user login as sender or receiver. below is my table for chats between sender and receiver for particular item id

I want the solution like each item id last/latest message between sender and receiver and need single row only to to show chat list.

I tried this query but its returning only single row but i want all rows where user have new message.

SELECT id,
   `sender`,
   `receiver`,
   `message`, 
   `created_at`
FROM chats
WHERE id IN (SELECT MAX(id) 
             FROM chats
             WHERE `sender` = 2 OR `receiver` = 2
             GROUP BY IF (`sender` = 2, `sender`, `receiver`))
ORDER BY `created_at` DESC;

Expected Result =>

Answers

To achieve the desired result, you can use a subquery to identify the latest message for each item ID where the user has either sent or received a message. Then, you can join this subquery with the chats table to retrieve the full details of the latest message for each item ID. Here's how you can do it:

SELECT c.id,
       c.sender,
       c.receiver,
       c.message,
       c.created_at
FROM chats c
JOIN (
    SELECT id,
           MAX(created_at) AS latest_created_at
    FROM chats
    WHERE sender = 2 OR receiver = 2
    GROUP BY id
) AS latest_messages ON c.id = latest_messages.id AND c.created_at = latest_messages.latest_created_at
ORDER BY c.created_at DESC;

In this query:

1. The subquery selects the id of each chat along with the maximum created_at timestamp for messages where the user has either sent or received a message (with the condition sender = 2 OR receiver = 2).
2. Then, in the main query, we join the chats table with the subquery based on both the id and the created_at timestamp to retrieve the full details of the latest message for each item ID.
3. Finally, we order the results by the created_at timestamp in descending order to display the latest messages first.

This query will return all rows where the user has new messages, with each row representing the latest message for each item ID.