Intersection of two relations

Question

Say I have two relations that hold records in the same model, such as:

@companies1 = Company.where(...)
@companies2 = Company.where(...)

How can I find the intersection of these two relations, i.e. only those companies that exist within both?

Answer 1

I solve similar problem this way

Company.connection.unprepared_statement do
  Company.find_by_sql "#{@companies1.to_sql} INTERSECT #{@companies2.to_sql}"
end

We need unprepared_statement block here because latest Rails versions use prepared statements to speed up arel queries, but we need pure SQL in place.

Answer 2

By default connecting those where together creates AND which is what you want.

So many be:

class Company < ActiveRecord::Base
  def self.where_1
    where(...)
  end
  def self.where_2
    where(...)
  end
end

@companies = Company.where_1.where_2

====== UPDATED ======

There are two cases:

# case 1: the fields selecting are different
Company.where(:id => [1, 2, 3, 4]) & Company.where(:other_field => true)
# a-rel supports &, |, +, -, but please notice case 2

# case 2
Company.where(:id => [1, 2, 3]) & Company.where(:id => [1, 2, 4, 5])

# the result would be the same as
Company.where(:id => [1, 2, 4, 5])
# because it is &-ing the :id key, instead of the content inside :id key

So if you are in case 2, you will need to do like what @apneadiving commented.

Company.where(...).all & Company.where(...).all

Of course, doing this sends out two queries and most likely queried more results than you needed.

Answer 3

Use sql keyword INTERSECT.

params1 = [1,2,4]
params2 = [1,3,4]
query = "
SELECT companies.* FROM companies
WHERE id in (?,?,?)
INTERSECT
SELECT companies.* FROM companies
WHERE id in (?,?,?)
"
Company.find_by_sql([query, *params1, *params2])

it will be faster than previous solution.