How to make a query to get all groups of a user, showing the users that each group has (postgresql)

Question

This is an issue that I thought easy at first, but haven't been able to solve it.

I got a table users(id, username, email) and another table groups(id, name). I got a third table that stores the many-to-many relations between the other two, groups_users(groupId, userId).

I want to make a query to get the groups which a certain user is member of. I also want that those queried groups contain the nested data of their respective users. Translated to JSON, it would be something like:

[
  {
    id: 1,
    name: 'foo group',
    users: [
      {
        id: 1,
        name: 'bar',
        email: '[email protected]'
      }
   ]
]

Any idea of the raw query I should make to Postgres for achieving this? I know that I would have to use funcionality of Postgres, that it also part of the question. Thanks in advance.

Answer 1

Figure out what which groups the user is part of (g). Then join that with groups_users to get those user_ids and join that with users to user_info.

select *
from (
  groups_users natural join 
  (select groupId from groups_users where userId = ?) g
)
join users on groups_users.userId = users.id

If you need the group name, join with that too.

Here is another way:

select *
from groups_users
join users on group_users.userId = users.id
where groups_users.gropId in (select groupId from groups_users where userId = ?)