Fetch multi-participant conversations with last message for each

I am trying to create a simple chat application database schema, and query the conversations. My current table setup is the following:

CREATE TABLE chat_user (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    display_name VARCHAR(140),
    ... other user stuff ...
);

CREATE TABLE conversation (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title VARCHAR(140),
    created timestamp with time zone NOT NULL
);

CREATE TABLE conversation_message (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    conversation_id bigint NOT NULL,
    sender_id bigint NOT NULL,
    body TEXT NOT NULL,
    created timestamp with time zone NOT NULL
);

CREATE TABLE conversation_participant (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    conversation_id bigint NOT NULL,
    user_id bigint NOT NULL
);

So basically each conversation has its own title, and multiple participants. What I would like to fetch my conversations sorted by the date of the latest message in the conversation (so the conversations with the newest messages are shown first). The result set should contain the id, title of the conversation and list of participants + the id, sender_id and body of the latest message.

It would also be required to fetch the conversations paginated based on the creation date of the conversation (20 per page)

Is my table setup efficient enough to satisfy the above constraint? Seems to me that this could result in a rather large query with multiple subqueries?.

Answer

To get latest message for your conversations there are ways to achieve it like self joins or window functions (row_number(), rank() etc). Using window function you can write your query as

with cm as (
  select *,
  rank() over (partition by conversation_id order by created desc) as r
  from conversation_message
)

select  c.id, 
        c.title, 
        cm.body,
        cm.created,
        cm.r,
        cu.display_name
from conversation as c
left join cm on c.id = cm.conversation_id and cm.r  <= 1
left join chat_user cu on cu.id = cm.sender_id

DEMO

In above query I have used left joins to include conversions with no messages, If you need only conversations which has messages then use inner joins. If you need more than 1 latest messages for each conversation change cm.r <= @no

To get participants list for each conversation you can add new CTE like

with cm as (
  select *,
    rank() over (partition by conversation_id order by created desc) as r
  from conversation_message
),
 message_participants as (
  select
    m.conversation_id,
    array_agg(u.display_name order by m.created desc) as participants
  from chat_user as u
  join conversation_message as m on u.id = m.sender_id
  group by m.conversation_id
)

select  c.id, 
        c.title, 
        cm.body,
        cm.created,
        cm.r,
        cu.display_name,
        cmp.participants
from conversation c
left join cm on c.id = cm.conversation_id and cm.r  <= 1
left join chat_user cu on cu.id = cm.sender_id
left join message_participants cmp on c.id = cmp.conversation_id

DEMO

Improvements

• Add user_id in conversation table to identify who has created this conversation.

• Table conversation_participant is redundant while you can extract list of participants from conversation_message