Postgresql query to retrieve conversation, last message in conversation, last message and unread count

I have the following tables:

users Table

id (uuid)
someuuid
someuuid

ads Table

id	owner_id(fkey to users)
someuuid	someuuid
someuuid	someuuid

messages Table

id	ad	sender(fkey users)	receiver(fkey users)	content	created_at	read_at (null means unseen)
someuuid	someuuid	someuuid	sometext	somedate	somedate

Which means every message is related to an ad, and the sender or receiver is the owner of the ad.

Now i want to query for each user (In order to show in the inbox) every conversation they are member of, the content of the last message, the date that last message was created in that conversation and the amount of messages that user has not yet read in that conversation.

So to resume, every conversation between 2 users is about an ad posted by either the sender or the receiver of the message

The furthest i got is to be able to retrieve every conversation, last message in that convo and the creation date of the last message using the following query

SELECT *
FROM messages
    , (
        SELECT MAX(created_at) AS last_message_sent_at
        FROM messages
        WHERE (
                messages.sender = '80d1c6070a7d' -- ID to compare with (logged in users's ID)
           OR messages.receiver = '80d1c6070a7d' -- ID to compare with (logged in users's ID) 
                )
        GROUP BY ad
        ) AS conversations
WHERE created_at = conversations.last_message_sent_at
ORDER BY messages.created_at DESC LIMIT 100 OFFSET 0

Solution

To provide an example of what is possible, please re-consider the details given in the question. Below is some assumed sample data that enables simulation of your problem and a potential query to solve it. However it is easy to make mistakes when we just don't know what your data actually is like, so, with many caveats, consider the example below. (nb, I used varchar(20) to avoid issues trying to simulate UUIDs):

DDL (so we know what we are dealing with)

CREATE TABLE users (
    id varchar(20) PRIMARY KEY,
    name VARCHAR(100)
);
 
CREATE TABLE ads (
    id varchar(20) PRIMARY KEY,
    owner_id varchar(20) REFERENCES users(id)
);

CREATE TABLE messages (
    id varchar(20) ,
    ad varchar(20) REFERENCES ads(id),
    sender varchar(20) REFERENCES users(id),
    receiver varchar(20) REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP,
    read_at TIMESTAMP
);

sample data (assumed)

INSERT INTO users (id, name) VALUES
('80d1c6070a7d', 'User1'),
('someuuid2', 'User2'),
('someuuid3', 'User3');

INSERT INTO ads (id, owner_id) VALUES
('ad1', '80d1c6070a7d'),
('ad2', 'someuuid2'),
('ad3', 'someuuid3');


INSERT INTO messages (id, ad, sender, receiver, content, created_at, read_at) VALUES
('msg1', 'ad1', '80d1c6070a7d', 'someuuid3', 'Hello', '2023-11-27 10:00:00', '2023-11-27 10:05:00'),
('msg2', 'ad1', 'someuuid2', '80d1c6070a7d', 'Hi', '2023-11-27 10:02:00', NULL),
('msg3', 'ad2', 'someuuid3', '80d1c6070a7d', 'How are you?', '2023-11-27 11:00:00', NULL),
('msg4', 'ad3', '80d1c6070a7d', 'someuuid2', 'Fine, thank you', '2023-11-27 12:00:00', NULL);

Query

SELECT
      u.id AS user_id
    , u.name AS user_name
    , m.ad AS ad_id
    , a.owner_id AS ad_owner_id
    , m.content AS last_message_content
    , m.created_at AS last_message_created_at
    , COUNT(CASE WHEN m.read_at IS NULL THEN 1 END) AS unread_messages_count
FROM users u
JOIN messages m ON u.id = m.sender
    OR u.id = m.receiver
JOIN ads a ON m.ad = a.id
WHERE u.id = '80d1c6070a7d' -- ID to compare with (logged in user's ID)
GROUP BY
      u.id
    , u.name
    , m.ad
    , a.owner_id
    , m.content
    , m.created_at
ORDER BY
      m.created_at DESC

Result

user_id	user_name	ad_id	ad_owner_id	last_message_content	last_message_created_at	unread_messages_count
80d1c6070a7d	User1	ad3	someuuid3	Fine, thank you	2023-11-27 12:00:00	1
80d1c6070a7d	User1	ad2	someuuid2	How are you?	2023-11-27 11:00:00	1
80d1c6070a7d	User1	ad1	80d1c6070a7d	Hi	2023-11-27 10:02:00	1
80d1c6070a7d	User1	ad1	80d1c6070a7d	Hello	2023-11-27 10:00:00	0

Summary of conversations

WITH ranked_messages
AS (
    SELECT
          m.ad AS ad_id
        , m.content AS message_content
        , m.created_at AS message_created_at
        , COUNT(CASE WHEN m.read_at IS NULL THEN 1 END) OVER (PARTITION BY m.ad) AS unread_messages_count
        , ROW_NUMBER() OVER (
            PARTITION BY m.ad ORDER BY m.created_at DESC
            ) AS rn
    FROM messages m
    WHERE (
            m.sender = '80d1c6070a7d'
            OR m.receiver = '80d1c6070a7d'
            )
    )
SELECT
      u.id AS user_id
    , u.name AS user_name
    , rm.ad_id
    , a.owner_id AS ad_owner_id
    , rm.message_content AS last_message_content
    , rm.message_created_at AS last_message_created_at
    , rm.unread_messages_count
FROM users u
JOIN ranked_messages rm ON u.id = '80d1c6070a7d'
JOIN ads a ON rm.ad_id = a.id
WHERE rm.rn = 1
ORDER BY
      m.created_at DESC

Result

user_id	user_name	ad_id	ad_owner_id	last_message_content	last_message_created_at	unread_messages_count
80d1c6070a7d	User1	ad3	someuuid3	Fine, thank you	2023-11-27 12:00:00	1
80d1c6070a7d	User1	ad2	someuuid2	How are you?	2023-11-27 11:00:00	1
80d1c6070a7d	User1	ad1	80d1c6070a7d	Hi	2023-11-27 10:02:00	1

fiddle

Here is a PostgreSQL specific solution using select distinct on(...) this feature allows you to summarise rows so that only one row per the columns specified in parentheses, after "on". Then, you should (really MUST) use the order by clause to determine which specific row that matches the "on" columns is presented. In other words select distinct on(...) chooses a row with the order by clause determining which row (here it is the "most recent" message):

select distinct on (u.id, m.ad) 
      u.id AS user_id
    , u.name AS user_name
    , m.ad AS ad_id
    , a.owner_id AS ad_owner_id
    , m.content AS last_message_content
    , m.created_at AS last_message_created_at
    , COUNT(CASE WHEN m.read_at IS NULL THEN 1 END) OVER (PARTITION BY m.ad) AS unread_messages_count
FROM users u
JOIN messages m ON u.id = m.sender
    OR u.id = m.receiver
JOIN ads a ON m.ad = a.id
WHERE u.id = '80d1c6070a7d' -- ID to compare with (logged in user's ID)
ORDER BY
      u.id, m.ad, m.created_at DESC

Result

user_id	user_name	ad_id	ad_owner_id	last_message_content	last_message_created_at	unread_messages_count
80d1c6070a7d	User1	ad1	80d1c6070a7d	Hi	2023-11-27 10:02:00	1
80d1c6070a7d	User1	ad2	someuuid2	How are you?	2023-11-27 11:00:00	1
80d1c6070a7d	User1	ad3	someuuid3	Fine, thank you	2023-11-27 12:00:00	1

fiddle