How to write set-operation queries in ActiveRecord without writing INTERSECT, UNION, or EXCEPT in SQL?

Question

I have a Rails app for managing volunteers and I'm looking for 'the Rails way' of writing a complex SQL query for filtering all of the volunteer records. I have the following models:

class Volunteer < ActiveRecord::Base
  has_many :volunteer_skills
  has_many :skills, through: :volunteer_skills
  has_many :volunteer_lists
  has_many :lists, through: :volunteer_lists
end

class VolunteerSkill < ActiveRecord::Base
  belongs_to :volunteer
  belongs_to :skill
end

class VolunteerList < ActiveRecord::Base
  belongs_to :volunteer
  belongs_to :list
end

If @lists is an array of List IDs and @skills is an array of Skill IDs

1. I want to find all volunteers on ANY @lists WITHOUT ANY of @skills.
2. I want to find all volunteers on ANY @lists WITHOUT ALL of @skills.
3. I want to find all volunteers on ANY @lists WITH ANY of @skills.
4. I want to find all volunteers on ANY @lists WITH ALL of @skills.

After seeing this StackOverflow question, I created a solution to this problem using .find_by_sql. When the query involves finding volunteers with ALL @skills, I construct an INTERSECT query. When the query involves finding volunteers with ANY @skills, I construct a UNION query. When the query involves finding volunteers WITHOUT @skills, I format the query with EXCEPT.

Unfortunately, this solution is not NEARLY as friendly as one that makes use of ActiveRecord. I'm pretty new to programming, but the problem I'm trying to solve seems fairly straightforward/common. Is there a better solution that makes use of ActiveRecord?

I'm very grateful for any ideas!

Answer 1

You can do most of the work with ActiveRecord, although you still need to use some SQL fragments.

First, make a scope for all volunteers on any @lists:

class Volunteer < ActiveRecord::Base
  scope :on_any_list -> lists { joins(:volunteer_list).where(volunteer_id: lists) }
end

Then add conditions for each of your skills criteria.

Volunteers with any @skills are easy:

on_any_list(@lists).joins(:volunteer_skills).where(skill_id: @skills).distinct

For volunteers with all @skills, you need to build a query:

query = on_any_list(@lists)
@skills.each do |skill|
  query = query.where("exists (select 1 from volunteer_skills vs " +
                      "where vs.volunteer_id = volunteers.id and skill_id = ?)", skill)
end

Unfortunately ActiveRecord's not doesn't let you construct the opposite queries of either of the above, so you need to do the skills-lacking queries from scratch.

Volunteers without all of @skills:

on_any_list(@lists).
  where("not exists (select 1 from volunteer_skills vs " +
        "where vs.volunteer_id = volunteers.id and skill_id in (?))", @skills)

And finally, the trickiest, volunteers without any of @skills:

on_any_list(@lists).
  joins("left join volunteer_skills vs on volunteers.id = vs.volunteer_id").
  where(skill_id: @skills)
  where("volunteer_skills.id is null").
  distinct

(There is an AREL way to do the outer join, but I don't think it's easier.)

The diverse techniques needed to write and understand each of these makes me wonder whether, as a whole, they're really better than your SQL versions. At least the on_any_lists part works well.