in SQL Trying to return all users who are members of the same groups as a specific user

Question

This query is getting me a bit turned around. In a Postgresql (Express/Knex/Objection) database, I have three tables, Users, Groups and an associative table Memberships s.t. Users have_many Groups through Memberships.

For a specific User's Groups, I want to return all other Users who have Memberships with those Groups.

to answer the questions below, given one user I want to return all other distinct users who belong to any group which also includes the first user.

I think I am getting flummoxed by the fact that I have Users on both sides of the join. I imagine people have solved this multiple times over, but I am not finding any examples online. Per requests, including table schema:

Users:

Groups:

Memberships:

perhaps provide your table structures with representative columns — Charlie K, Feb 24 '20 at 21:48
Please show us a minimal and representative sample data from all 3 tables, along with the desired results, and you current attempt. — GMB, Feb 24 '20 at 21:49
Your question is unclear. Do you want users that overlap in ANY of the groups? In ALL of the groups? Exactly match the SAME groups? — Gordon Linoff, Feb 24 '20 at 22:06
updated above. looking for collection of distinct users who belong to any group containing a specified user — user2799827, Feb 24 '20 at 23:54

Mark Taylor · Accepted Answer · 2020-02-25T11:11:37.237

0

Ok the comment makes more sense now and I've edited the solution accordingly.

select 
    distinct u.user.id

from users u
join memberships m on u.id = m.users_id

where m.groups_id in 

    (//this is the subquery to get the groups of the initial user)
    select 
        m.groups_id

    from memberships m

    where m.users_id = [user_1] //this is where you set your initial user
    ) subQuery

There are a few ways to do this but this is in a traditional SQL style and broken down as per your comment.

This will return just a distinct list of users as requested - add additional columns in the top select for additional detail as required.

edited Feb 25 '20 at 11:11

answered Feb 25 '20 at 02:56

Mark Taylor

1,128
8
15

I want to start with a specific user (user_1). Then I want to find all of the groups that user_1 is a member of (user_1's groups). Finally, I want the query to return all of the distinct users who are members of user_1's groups. – user2799827 Feb 25 '20 at 08:48
Ok I see what you're after now - edited the answer above. – Mark Taylor Feb 25 '20 at 11:12
I am getting the error `missing FROM-clause entry for table "user"` – user2799827 Feb 25 '20 at 17:15
You should be able to fix trivial syntax errors yourself! That's probably just a question of alias' in whatever engine you're using! Here's a SQL Fiddle link for you: http://sqlfiddle.com/#!9/e26520/1 – Mark Taylor Feb 26 '20 at 03:58
The fiddle's simplified version works exactly as needed. Thanks! – user2799827 Feb 26 '20 at 12:40

Rashomon · Answer 2 · 2020-02-25T10:20:29.343

At your Objection User model, define the relation between users and groups:

  static relationMappings = {
    groups: {
      relation: Model.ManyToManyRelation, // An user has many groups
      modelClass: Group,
      join: {
        from: 'users.id',
        through: {
          from: 'memberhips.users_id',
          to: 'memberhips.groups_id'
        },
        to: 'groups.id'
      }
    }
  }

Then, at your Objetion Group model, define the realtions between groups and users:

  static relationMappings = {
    users: {
      relation: Model.ManyToManyRelation, // A group has many users
      modelClass: User,
      join: {
        from: 'groups.id',
        through: {
          from: 'memberhips.groups_id',
          to: 'memberhips.users_id'
        },
        to: 'users.id'
      }
    }
  }

Then use eager loading to load the relations:

User.query().eager('groups.[users]').findById(userId)

The query should return a structure like this (some properties like address are ignored):

User {
  id: 3,
  name: 'name',
  description: 'description',
  groups: [ // User 3 has two groups: 1 and 2
    {
        id: 1,
        name: 'name1',
        users: [ // There are two member of group, 3 and 6
            {
                id: 3,
                name: 'name',
                description: 'description'
            },
            {
                id: 6,
                name: 'other',
                description: 'other'
            }
        ]
    },
    {
        id: 2,
        name: 'name2',
        users: [
            {
                id: 3,
                name: 'name',
                description: 'description'
            },
            {
                id: 7,
                name: 'another',
                description: 'another'
            }
        ]
    },
  ]
}

Not sure if its possible directly in the query (I would say no). You want to retrieve all the users that share groups with the own user? In my example: user 6 and user 7? — Rashomon, Feb 25 '20 at 20:25
exactly, in this case I want to return users 6 and 7. Would I need to just parse this json result and extract user 3 and any duplicates? — user2799827, Feb 26 '20 at 12:23
Exactly. Personally I find this way more clean and scalable. If some colleage needs to apply changes to the behaviour, it can be done easily without dealing with the complex query syntax. — Rashomon, Feb 26 '20 at 14:43

in SQL Trying to return all users who are members of the same groups as a specific user

2 Answers2