Hoping someone can solve this for me

Tables

users

id,
...

messages

id, 
sender_id -> users.id,
...

message_recipients

message_id -> messages.id, 
recipient_id -> users.id,
...

When given some user_id I want to get all conversations for that users

conversation data could look like

message_count, 
other_users_ids -> array of users.id

CodePudding user response：

If I got you right, just this way:

SELECT users.id, COUNT(1), array_agg(message_recipients.recipient_id) FROM users
INNER JOIN messages ON users.id = messages.sender_id
INNER JOIN message_recipients on messages.id = message_recipients.message_id
GROUP BY users.id;