Rails: Find multiple records with same parent_id

I want to find out which groups have the same 2 users (or 3 or so on), then with the next records:

# Users         # Groups

id | name       id | name

1  | Jhon       1  | Rock
2  | Mike       2  | Pop
3  | Sean       3  | Jazz

# Group Users

id | group_id   | user_id
1  | 1 (Rock)   | 1 (Jhon)
2  | 1 (Rock)   | 2 (Mike)
3  | 1 (Rock)   | 3 (Sean)
4  | 2 (Pop)    | 1 (Jhon)
5  | 2 (Pop)    | 2 (Mike)
6  | 3 (Jazz)   | 1 (Jhon)
7  | 3 (Jazz)   | 3 (Sean)

How can I achieve the next examples:

users = [1,2] # (Jhon, Mike)
GroupUser.where(user_id: users)... uniq.pluck(:group_id)
# [1,2] (Rock,Pop)

users = [2,3] # (Mike, Sean)
GroupUser.where(user_id: users)... uniq.pluck(:group_id)
# [1] (Rock)

users = [1,3] # (Jhon, Sean)
GroupUser.where(user_id: users)... uniq.pluck(:group_id)
# [1,3] (Rock,Jazz)

UPDATE (Of course i have associations)

class User < ActiveRecord::Base
    has_many :user_groups
end

class Group < ActiveRecord::Base
    has_many :user_groups
end

class UserGroup < ActiveRecord::Base
    belongs_to :group
    belongs_to :user
end

3 answers

  • answered 2018-02-13 04:25 mindlis

    If you just want the group_ids, this should work: GroupUser.joins(:user).where(users: { id: [1, 2, 3] }).pluck(:group_id)

    Otherwise, I would suggest also making has_many associations on User and Group. Then you could do something like Group.joins(:users).where(users: { id: [1, 2] }).distinct and get the actual Group records.

  • answered 2018-02-13 07:18 Tai

    You can achieve what you want by grouping GroupUser by group_id then select only group have number of users equal to number of expected user. Try below query:

    user_ids = [1, 2]
    group_ids = GroupUser.having("COUNT(*) = #{user_ids.count}").group(:group_id).where(user_id: user_ids).uniq.pluck(:group_id)
    

  • answered 2018-02-13 16:32 praga2050

    You have to declare relationship like this below

    class User < ApplicationRecord
    
    
      has_many :group_users
    
      has_many :groups,
               through: :group_users
    end
    
    
    class GroupUser < ApplicationRecord
    
      belongs_to :user 
      belongs_to :group
    
    end
    
    class Group < ApplicationRecord
    
      has_many :group_users
    
      has_many :users,
               through: :group_users
    end
    

    My Rails console Output for No of Users under a group. You can use the same to find out which groups have the same multiple users

    > g = Group.find 1
      Group Load (0.8ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."id" = ? LIMIT ?  [["id", nil], ["LIMIT", nil]]
     => #<Group id: 1, name: "Rock", created_at: "2018-02-13 16:06:22", updated_at: "2018-02-13 16:06:22"> 
    
    
     > g.users.count
       (0.6ms)  SELECT COUNT(*) FROM "users" INNER JOIN "group_users" ON "users"."id" = "group_users"."user_id" WHERE "group_users"."group_id" = ?  [["group_id", nil]]
     => 2 
    
    
    
    > Group.joins(:users).where(users: {id: [1, 2] } ).select('groups.id').uniq.count
      Group Load (0.5ms)  SELECT groups.id FROM "groups" INNER JOIN "group_users" ON "group_users"."group_id" = "groups"."id" INNER JOIN "users" ON "users"."id" = "group_users"."user_id" WHERE "users"."id" IN (1, 2)
     => 1