CODEWITHSUNDEEP
sqlruby-on-railssqlitepostgresql
Eric
Eric

Reputation: 636

Find list of groups where at least one member is part of list (rails, SQL)

I have a simple has_many :through arrangement, as shown below

# employee.rb
class Employee < ActiveRecord::Base
  has_many :group_assignments
  has_many :groups, through: :group_assignments
  # ...
end

# group.rb
class Group < ActiveRecord::Base
  has_many :group_assignments
  has_many :employees, through: :group_assignments
  # ...
end

# group_assignment.rb
class GroupAssignment < ActiveRecord::Base
  belongs_to :employee
  belongs_to :group
end

I have a list of employees. For that list, I want to grab every group that contains at least one of the employees on that list. How would I accomplish this in a manner that isn't horridly inefficient? I'm newish to Rails and very new at SQL, and I'm pretty at a loss. I'm using SQLite in development and PostgreSQL in production.

Upvotes: 0

Views: 113

Answers (3)

Chris Salzberg
Chris Salzberg

Reputation: 27374

For a list of employees named employees_list, this will work:

 Group.includes(:employees).where('employees.id' => employees_list.map(&:id))

This is roughly the kind of SQL you will get:

SELECT "groups"."id" AS t0_r0,
  "groups"."created_at" AS t0_r1, "groups"."updated_at" AS t0_r2,
  "employees"."id" AS t1_r0, "employees"."created_at" AS t1_r1, "employees"."updated_at" AS t1_r2
  FROM "groups"
  LEFT OUTER JOIN "group_assignments" ON "group_assignments"."group_id" = "groups"."id"  
  LEFT OUTER JOIN "employees" ON "employees"."id" = "group_assignments"."employee_id" 
  WHERE "employees"."id" IN (1, 3)

So what is happening is that groups and group_assignments tables are first being joined with a left outer join (matching the group_id column in the group_assignments table to the id column in the groups table), and then employees again with a left outer join (matching employee_id in the group_assignments table to the id column in the employees table).

Then after that we're selecting all rows where 'employees'.'id' (the id of the employee) is in the array of employees in the employee list, which we get by mapping employees_list to their ids using map: employees_list.map(&:id). The map(&:id) here is shorthand for: map { |e| e.id }.

Note that you could use joins instead of includes here, but then you would get duplicates if one employee is a member of multiple groups. Kind of subtle but useful thing to know.

Hope that makes sense!

Upvotes: 2

shweta
shweta

Reputation: 8169

try

Group.joins(:group_assignments).where("group_assignments.employee_id in (?)", @employees.map(&:id))

Upvotes: 0

Mark Swardstrom
Mark Swardstrom

Reputation: 18080

This is the general idea, but depending on your data, you may need to select distinct.

Group.includes(:group_assignments => :employee).where(:employee => {:id => ?}, @employees.map(&:id))

Upvotes: 0

Related Questions