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

Please update question to post sample data as [formatted text](https://senseful.github.io/text-table/) or even better a [fiddle](https://dbfiddle.uk/?rdbms=postgres_14) - **not as an image**. Also include table definition (ddl script) as text. — Belayer, Aug 12 '22 at 04:04
does your result include `from_user_id: 1` and `to_user_id: 4`? — Deepan, Aug 12 '22 at 04:26
@Deepan, no, I find a solution that works, I use CTE and UNION, But I don't know if it is a good solution or not — Hadi Farhadi, Aug 12 '22 at 05:18
Follow the link above. Copy and Past your data into the *input* box, click *create table*. Now copy the *output* box. Tab back to your question and enter 2 lines each containing ``` only. Paste between then. — Belayer, Aug 12 '22 at 18:07

score 1 · Accepted Answer · answered Aug 12 '22 at 05:42

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.

I don't know ablout performance but it's better to user window function, I have tried some using that https://onecompiler.com/postgresql/3ycrtmcuf check this url — Deepan, Aug 12 '22 at 06:28
@Deepan thank you for your great effort. I understand your answer thanks. If you worry about IN statement, It is optimized in Postgres( I don't know from which version) — Hadi Farhadi, Aug 12 '22 at 14:06

SQL: Get the latest message for the current user

1 Answers1