CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4
Miguel Peniche
Miguel Peniche

Reputation: 1032

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

Upvotes: 0

Views: 740

Answers (3)

praga2050
praga2050

Reputation: 773

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

Upvotes: 0

Tai
Tai

Reputation: 1254

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)

Upvotes: 1

mindlis
mindlis

Reputation: 1677

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.

Upvotes: 0

Related Questions