Query intersection with activerecord

Question

I'd really like to do the following query with the help with active record

(select *
from people p join cities c join services s
where p.city_id = c.id and p.id = s.person_id and s.type = 1)

intersect

(select *
from people p join cities c join services s
where p.city_id = c.id and p.id = s.person_id and s.type = 2)

Problem is, first of all, mysql doesn't support intersect. However, that can be worked around of. The thing is that I can get active record to output anything even close to that.

In active record the best I could do was to issue multiple queries then use reduce :& to join them, but then I get an Array, not a Relation. That's a problem for me because I want to call things like limit, etc. Plus, I think it would be better to the intersection to be done by the database, rather than ruby code.

Answer 1

Your question is probably solvable without intersection, something like:

Person.joins(:services).where(services: {service_type: [1,2]}).group(
   people: :id).having('COUNT("people"."id")=2')

However the following is a general approach I use for constructing intersection like queries in ActiveRecord:

class Service < ActiveRecord::Base
  belongs_to :person

  def self.with_types(*types)
    where(service_type: types)
  end
end

class City < ActiveRecord::Base
  has_and_belongs_to_many :services
  has_many :people, inverse_of: :city
end

class Person < ActiveRecord::Base
  belongs_to :city, inverse_of: :people

  def self.with_cities(cities)
    where(city_id: cities)
  end

  def self.with_all_service_types(*types)
    types.map { |t|
      joins(:services).merge(Service.with_types t).select(:id)
    }.reduce(scoped) { |scope, subquery|
      scope.where(id: subquery)
    }
  end
end

Person.with_all_service_types(1, 2)
Person.with_all_service_types(1, 2).with_cities(City.where(name: 'Gold Coast'))

It will generate SQL of the form:

SELECT "people".*
  FROM "people"
 WHERE "people"."id" in (SELECT "people"."id" FROM ...)
   AND "people"."id" in (SELECT ...)
   AND ...

You can create as many subqueries as required with the above approach based on any conditions/joins etc so long as each subquery returns the id of a matching person in its result set.

Each subquery result set will be AND'ed together thus restricting the matching set to the intersection of all of the subqueries.

UPDATE

For those using AR4 where scoped was removed, my other answer provides a semantically equivalent scoped polyfil which all is not an equivalent replacement for despite what the AR documentation suggests. Answer here: With Rails 4, Model.scoped is deprecated but Model.all can't replace it