How to construct SQL query to select chat participants

I have 3 tables. Users(id, fullname), Chats(id, name) and Chats_Participants(chatId, userId). I need to select all the chats in which user with specified id consists. For example:

Chats:
1. 1, ‘Test’

Users:
1. 1, ‘Test user’
2. 2, ‘Test user2’

Chat_Participants:
1. 1(chatId), 1(userId)
2. 1(chatId), 2(userId)

As a result, I need something like this:
1(chatId) ‘Test'(chatName) participants(array of users in chat)

First I’ve wrote this:

select chats.*, json_agg(users) as participants
    from chats
           inner join chats_participants c2 on chats.id = c2."chatId"
           inner join users on c2."userId" = users.id
    where users.id = $userId
    group by chats.id;

but this query selects only one participant

Answer

You can try with array_agg in postgresql. Demo here. Users table is not linked here, printing user id from chat_participants table instead.

SELECT chats.chat_id,chats.name, array_agg(userid order by chats.chat_id) 
FROM chats 
INNER JOIN chat_participants ON chats.chat_id = chat_participants.chat_id
GROUP BY chats.chat_id,chats.name

Advertisement

Answer