ActiveRecord: Search for multiple ids in association with AND condition

Question

I have the following models

class Employee < ApplicationRecord
   has_many :employee_skills
   has_many :skills, throught: :employee_skills
end

class Skill < ApplicationRecord
   has_many :employee_skills
   has_many :employees, through: :employee_skills
end

class EmployeeSkill < ApplicationRecord
   belongs_to :employee
   belongs_to :skill
end

How can i query for employees which have skill 1 AND 2 AND 3 (or more skills). Array conditions (see Rails Guide) selects with OR not with AND.

Sebastián Palma · Answer 1 · 2020-08-10T11:19:17.130

1

One possible way is using your own custom "raw" join condition in joins:

Employee
  .joins('INNER JOIN skills s1 ON s1.id = 1 AND s1.employee_id = employees.id')
  .joins('INNER JOIN skills s2 ON s2.id = 2 AND s2.employee_id = employees.id')

Here s1.id and s2.id are both the skills ids you have.

edited Aug 10 '20 at 11:19

answered Aug 10 '20 at 11:01

Sebastián Palma

32,692
6
40
59

this would only work for exactly 2 skills, but not for more or less. It doesn't work dynamicaly, right? – fydelio Aug 12 '20 at 10:23
I'm afraid there's nothing out of the box in Rails to work with this kind of joins. Rails API for joins is quite poor, you can create your own string and pass it to `joins` @fydelio. – Sebastián Palma Aug 12 '20 at 10:31
I think the solution has to be something with GROUP_BY and HAVING. https://stackoverflow.com/questions/4047484/selecting-with-multiple-where-conditions-on-same-column. Will try to figure this out now. – fydelio Aug 12 '20 at 12:12

Tanay Sharma · Answer 2 · 2020-08-10T10:26:55.037

0

You can simply use includes if you want to apply for all employees and for single employee you can do like this

@employee.skills.where(id: array_of_ids)

With Includes for all or more than 1 employees

Employee.includes(:skills).where('skills.id' => array_of_ids)

As per @sebastian comment, you can use joins

Employee.joins(:skills).where(skills: { id: [1,2,3,4] })

edited Aug 10 '20 at 10:26

answered Aug 10 '20 at 09:02

Tanay Sharma

1,098
10
29

score 0 · Accepted Answer · answered Aug 12 '20 at 13:02

Inspired by the solution here: SELECTING with multiple WHERE conditions on same column, this is the solution using only active records

Employee.joins(:employee_skills).where(employee_skills: {skill_id: @skills.ids})
        .group('employees.id, employee_skills.employee_id')
        .having('COUNT(*) = ?', @skills.count)

ActiveRecord: Search for multiple ids in association with AND condition

3 Answers3

Linked