Intersecting queries with activerecord

Question

I would really like to make the following request using the 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) 

The problem is, first of all, that mysql does not support intersection. However, this can be circumvented. The fact is that I can get an active record to bring something even close to this.

In an active record, it was best to make a few queries and then use reduce :& to join them, but then I get an array, not a connection. This is a problem for me, because I want to name things like limit, etc. In addition, I think it would be better to cross with the database, and not with ruby-code.

Answer 1

Your question will probably be resolved without intersection, something like:

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

However, the following general approach that I use to build intersections, such as 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 forms:

 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 needed using the above approach based on any conditions / joins, etc., as long as each subquery returns the identifier of the matching person in its result set.

Each result set in the subquery will be equal to AND, however, limiting the set of correspondence to the intersection of all subqueries.

UPDATE

For those using AR4 where scoped was removed, my other answer contains a semantically equivalent scoped polyfil, which all not an equivalent replacement, despite the fact that the AR documentation offers. The answer is here: With Rails 4, Model.scoped is deprecated, but Model.all cannot replace it

Answer 2

I struggled with the same problem and found only one solution: several unions against the same association. It may not be too rails-ish, since I am building an SQL string for joins, but I have not found another way. This will work for an arbitrary number of types of services (in the cities, apparently, there are no factors, therefore, for clarity, unification was excluded):

 s = [1,2] j = '' s.each_index {|i| j += " INNER JOIN services s#{i} ON s.person_id = people.id AND s#{i}.type_id = #{s[i]}" } People.all.joins(j) 

Answer 3

I am not very familiar with intersect, but is there a reason why you cannot simplify this for a single query and use IN instead?

 People.where(:services => {:type => [1,2]}).joins(:cities => :services) 

Update. You can bind methods and join AND:

 People.where(:services => {:type => 1}).where(:services => {:type => 2}).joins(:cities => :services)