SQL: Get the latest message for the current user

Question

I want to get the latest message for each user who chats with the current user

I wrote this code:

This is my data:

SELECT messages.id, messages.body, messages.created_at, messages.is_seen,
            o.username AS from_username, o.image_url AS from_image_url, o.gender AS from_gender,
            c.username AS to_username, c.image_url AS to_image_url, c.gender AS to_gender,

            (SELECT count(messages.id) from messages where messages.to_user_id = 1) AS unread_message_count

            FROM messages

            LEFT OUTER JOIN users AS o ON messages.from_user_id = o.id
            LEFT OUTER JOIN users AS c ON messages.to_user_id = c.id

            WHERE messages.id IN
                (SELECT MAX(messages.id) FROM messages
                    WHERE (messages.to_user_id = 1 OR messages.from_user_id = 1)
                    GROUP BY messages.from_user_id, messages.to_user_id)
            OFFSET 0 LIMIT 5

but it gives me two records for each user chat

for user_id = 1 the desired result is:

id ... from_user_id to_user_id
9 |    | 2          | 1
8 |    | 1          | 3
7 |    | 4          | 1

How can achieve the desired result?

Thanks to @Deepan to provides this

with t(id,body,created_at,is_seen,from_user_id,to_user_id) as (values
(1,'hello how are you man','2022-08-12 03:07:15',false,1,4),
(2,'are you okay','2022-08-12 03:08:14',false,1,4),
(3,'yesIknow you man','2022-08-12 03:08:25',false,1,4),
(4,'where are you from','2022-08-12 03:13:45',false,1,4),
(5,'Iamfromnothing','2022-08-12 03:13:51',false,1,4),
(6,'areyouok?','2022-08-12 03:13:58',false,1,4),
(7,'yesitis','2022-08-12 05:37:09',false,4,1),
(8,'thankyousomuch','2022-08-12 07:09:36',false,1,3),
(9,'youareverywelcome','2022-08-12 07:10:10',false,2,1)
)
select * from t 

and this is Online Postgresql Compiler

Answer 1

I use CTE and UNION to solve the problem:

WITH CTE AS (SELECT messages.id, messages.body, messages.created_at, messages.is_seen, messages.from_user_id AS user_id FROM 
            messages
            where messages.to_user_id = 1
            UNION
            SELECT messages.id, messages.body, messages.created_at, messages.is_seen, messages.to_user_id AS user_id FROM 
            messages
            WHERE messages.from_user_id = 1)

        SELECT CTE.id,
                CTE.body,
                CTE.created_at,
                CTE.is_seen,
                users.username,
                users.image_url,
                users.gender,

                (SELECT count(messages.id) from messages where messages.from_user_id = CTE.user_id AND messages.to_user_id = 1 AND messages.is_seen = FALSE ) AS unread_message_count
            
            FROM CTE
            LEFT OUTER JOIN users on users.id = CTE.user_id
            WHERE CTE.id IN 
            (SELECT MAX(CTE.id) FROM CTE GROUP BY CTE.user_id)
        OFFSET 0 LIMIT 5

we don't need the current user anymore, because we have its id, then, we can get its info (username, image_url, gender)

IF THIS ANSWER HAS A PERFORMANCE ISSUE, PLEASE LET ME KNOW, THANKS.