Tarlen Tarlen - 2 months ago 9
SQL Question

Duplicates results in query

I have an

apps
table. Each app has many
conversations
and
users
. A conversation has many
messages
and each message can either belong to a
visitor
or
user
and a
visitor
can have many conversations.

For each of my conversations, I want to attach the name and avatar of the user who most recently wrote in the conversation.

If no user has replied, then instead I'd like to grab the 3 most recently created user's
avatars
, along with the name of the app, and use these instead.

This is what I've got so far, but it returns multiple results for the same conversation id, and I haven't found a solution to getting the app users avatars

select
c.id,
c.last_message,
c.last_activity,
coalesce(last.display_name, a.name || ' Team') as name,
array_agg(last.avatar)
from messages m
left join conversations c on c.id = m.conversation_id
left join apps a on a.id = c.app_id
left join lateral (
select u.id, u.display_name, u.avatar
from users u
where u.id = m.user_id
) as last on true
where c.visitor_id = 'c6p77hu9v000a4zcth4lnefn9'
group by c.id, last.display_name, last.avatar, a.name
order by c.inserted_at desc


Any help is greatly appreciated

Answer

For each of my conversations, I want to attach the name and avatar of the user who most recently wrote in the conversation.

To do that, you can use a LATERAL subquery, but you also need to add ORDER BY in such way that the last message is first, then use LIMIT 1 to get only that last row. So, if I assume you have a column message_datetime in message table, which stores the date and time the message has been sent, you can use:

select
      c.id,
      c.last_message,
      c.last_activity,
      coalesce(last.display_name, a.name || ' Team') as name,
      last.avatar
from
    conversations c
    left join apps a on a.id = c.app_id
    left join lateral (
        select
            u.id, u.display_name, u.avatar
        from
            users u
            inner join messages m on u.id = m.user_id
        where
            c.id = m.conversation_id
        order by
            m.message_datetime desc
        limit 1
    ) as last on true
where
    c.visitor_id = 'c6p77hu9v000a4zcth4lnefn9'
order by
    c.inserted_at desc

If no user has replied, then instead I'd like to grab the 3 most recently created user's avatars, along with the name of the app, and use these instead.

That is simpler, as this query is uncorrelated to the previous. Assuming your users have an created_datetime column with the date and time the user has been created, you can use the simple query:

select
    u.id, u.display_name, u.avatar
from
    users u
order by
    u.created_datetime desc
limit 3

And so you can use it as a subquery in the previous query, using COALESCE to control which information to use:

select
      c.id,
      c.last_message,
      c.last_activity,
      coalesce(last.display_name, a.name || ' Team') as name,
      coalesce(array[last.avatar], last_all.avatar) as avatar
from
    conversations c
    left join apps a on a.id = c.app_id
    left join lateral (
        select
            u.id, u.display_name, u.avatar
        from
            users u
            inner join messages m on u.id = m.user_id
        where
            c.id = m.conversation_id
        order by
            m.message_datetime desc
        limit 1
    ) as last on true
    left join (
        select
            array_agg(u.avatar) as avatar
        from
            users u
        order by
            u.created_datetime desc
        limit 3
    ) last_all on true
where
    c.visitor_id = 'c6p77hu9v000a4zcth4lnefn9'
order by
    c.inserted_at desc
Comments