Rails query for a record with distinct set of associations

Question

I am trying to query for a record with a specific set of associations. It's a sort of existence query through a join. I want to query for a user who has exactly skill_1, skill_2, skill_3 with level_a, level_b, level_c

class User < ApplicationRecord
  has_many :skills
end

class Skill < ApplicationRecord
  belongs_to :user
end

# example of current attempt and intended use
def user_exists?(skills, levels)
  User.joins(:skills).where(skill_name: skills, skill_level: levels)
end

This will give Users where they have a skill IN skills with a level IN levels, which returns all kinds of combinations of skill and level sets, but I only want the Users with exactly those skills at exactly those levels.

How can I write that query?

Answer 1

So say I have skills = ['a','b','c'] and levels = [1,2,3].

Based on your question there are 2 possible iterpretations:

1. This a product of the 2 e.g. [["a", 1], ["a", 2], ["a", 3], ["b", 1], ["b", 2], ["b", 3], ["c", 1], ["c", 2], ["c", 3]]; or
2. This intended to be [["a", 1], ["b", 2], ["c", 3]]

Option 1: we can solve this as:

def user_exists?(skills, levels)
  User
   .joins(:skills)
   .where(skill_name: skills, skill_level: levels)
   .group(:id)
   .having(Arel.star.count.eq([skills.size,levels.size].max))
end

This will result in the following SQL:

SELECT 
  users.* 
FROM 
  users 
  INNER JOIN skills ON skills.user_id = users.id
WHERE 
  skills.name IN ('a','b','c') AND skills.levels IN (1,2,3) 
GROUP BY 
  users.id 
HAVING 
  COUNT(*) = 3 

Option 2: we can solve this as: (this assumes skills.size == levels.size)

def user_exists?(skills, levels)
  groups = skills.zip(levels).map do |g| 
    Arel::Nodes::Grouping.new(g.map(&Arel::Nodes.method(:build_quoted)))
  end
  User
    .joins(:skills)
    .where(Arel::Nodes::NamedFunction.new('ROW',
             [Skill.arel_table[:name],Skill.arel_table[:level]])
           .in(groups))
    .group(:id)
    .having(Arel.star.count.eq(skills.size))
end

This will result in the following SQL:

SELECT 
  users.* 
FROM 
  users 
  INNER JOIN skills ON skills.user_id = users.id
WHERE 
  ROW(skills.name,skills.level) IN (('a',1),('b',2),('c',3))
GROUP BY 
  users.id
HAVING 
  COUNT(*) = 3

Option 2 could also be written as:

 conditions = skills.zip(levels).map do |skill_name,level| 
   Arel::Nodes::Grouping.new(
     Skill.arel_table[:name].eq(skill_name).and(
       Skill.arel_table[:level].eq(level))
   )
  end.reduce(&:or)

  # sub: Arel::Nodes::NamedFunction.new('ROW', [Skill.arel_table[:name],Skill.arel_table[:level]]).in(groups)
  # with: groups

which would produce a WHERE clause for skills of

(skills.name = 'a' and skills.level = 1) OR 
(skills.name = 'b' and skills.level = 2) OR
(skills.name = 'c' and skills.level = 3)

There are other ways these queries can be accomplished using things like intersection queries but the construction is a bit less straight forward.