Refining the inner join when using includes in ActiveRecord

Question

How do I add a condition to the ON clause generated by includes in active record while retaining eager loading?

Let's say I have these classes:

class Car
   has_many :inspections
end

class Inspection
   belongs_to :car
end

Now I can do:

Car.includes(:inspections)

Select * from cars LEFT OUTER JOIN inspections ON cars.id = inspections.car_id

But I want to generate this sql:

Select * from cars LEFT OUTER JOIN inspections ON cars.id = inspections.car_id 
    AND inspections.month = '2013-04-01'

(this doesn't work):

Car.includes(:inspections).where("inspections.month = 2013-04-01")

Select * from cars LEFT OUTER JOIN inspections ON cars.id = inspections.car_id
    WHERE inspections.month = '2013-04-01'

Answer 1

I don't know this exactly, but what you are trying to do is probably not recommended i.e. violates one of Rails' conventions. According to this answer in a related question, the default behavior for such queries is to use two queries, like:

SELECT "cars".* FROM "cars";
SELECT "inspections".* FROM "inspections" WHERE "inspections"."car_id" IN (1, 2, 3, 4, 5);

This decision was made for performance reasons. That makes me guess that the exact type of query (JOIN or multiple queries) is an implementation detail that you cannot count on. Going along this train of thought, ActiveRecord::Relation probably wasn't designed for your use case, there is probably no way to add an ON condition in the query.

Going along this sequence of guesses, if you truly believe that your use case is unique, the best thing to do is probably for you to craft your own SQL query as follows:

Car.joins(sanitize_sql_array(["LEFT OUTER JOIN inspections ON inspections.car_id = cars.id AND inspections.month = ?", "2013-04-01"])

(Update: this was asked last year and did not receive a good answer.)

Alternative 1

As Carlos Drew suggested,

@cars   = Cars.all
car_ids = @cars.map(&:id) 
@inspections = Inspection.where(inspections: {month: '2013-04-01', car_id: car_ids})
# with scopes: Inspection.for_month('2013-04-01').where(car_id: car_ids)

However, in order to prevent car.inspections from triggering unnecessary SQL calls, you also need to do

# app/models/car.rb
has_many :inspections, inverse_of: :car

# app/models/inspection.rb
belongs_to :car, inverse_of: :inspections

Alternative 2

Perhaps you can find a way to cache the inspections for the current month, and then don't worry about eager loading. This might be the best solution, since the cache can be reused in various places.

@cars = Cars.all
@cars.each do |car|
  car.inspections.where(month: '2013-04-01')
end

Answer 2

The authors of Rails did not build this functionality into ActiveRecord, presumably because using WHERE returns the same result set, and they felt no need to have an alternative.

In the docs and code, we find the two "official" methods of adding conditions to included models.

In the actual source code: https://github.com/rails/rails/blob/5245648812733d2c31f251de3e05e78e68bfa3a5/activerecord/lib/active_record/relation/query_methods.rb we find them using WHERE to accomplish this:

And I quote: "

=== conditions

#
# If you want to add conditions to your included models you'll have
# to explicitly reference them. For example:
#
# User.includes(:posts).where('posts.name = ?', 'example')
#
# Will throw an error, but this will work:
#
# User.includes(:posts).where('posts.name = ?', 'example').references(:posts)

_END_QUOTE_

The docs mention another approach: http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html under the header "Eager loading of associations"

QUOTE:

If you do want eager load only some members of an association it is usually more natural to include an association which has conditions defined on it:

class Post < ActiveRecord::Base has_many :approved_comments, -> { where approved: true }, class_name: 'Comment' end

Post.includes(:approved_comments)

This will load posts and eager load the approved_comments association, which contains only those comments that have been approved.

END QUOTE

You can technically use such an approach, but it in your case it may not be so useful if you are using dynamic month values.

These are the only options, which in any case return the same results as your AND based query.

Answer 3

I've rethought your question more broadly. I think you are facing a code design problem as well as (instead of?) an ActiveRecord query problem.

You are asking to return a relation of Cars on which .inspections has been redefined to mean those Inspections matching a specific date. ActiveRecord does not allow you to redefine a model association on the fly, based on a query.

If you were not asking for a dynamic condition on the inspection date, I would tell you to use a has_many :through with a :condition.

has_many :passed_inspections, :through => :inspections, :conditions => {:passed => true}

@cars = Cars.includes(:passed_inspections)

Obviously, that would not work if you need to supply an inspection date on the fly.

So, in the end, I would tell you to do something like this:

@cars = Cars.all
@inspections = Inspection.where(inspections: {month: '2013-04-01', car_id: @cars.pluck(:id)})

(Exact, best implementation of that car_id where condition is up to debate. And you'll then need to group the @inspections by car_id to get the right subset in a given moment.)

Alternately, in a production environment, you might be able to rely on some fairly good/clever ActiveRecord caching. I'm not certain of this.

def inspections_dated(month)
  inspections.where(month: month)
end
Car.includes(:inspections).each{|car| car.inspections_dated(month).each.etc. }

Alternately, Alternately

You can, through manual SQL, trick ActiveRecord into giving you extended Car objects with an unclear interface:

@cars_with_insp = Car.join("LEFT OUTER JOIN inspections ON inspections.car_id = cars.id AND inspections.month = '2013-04-01'").select("cars.*, inspections.*")
@cars_with_insp.each{|c| puts c.name; puts c.inspection_month}

You'll see, in that .each, that you have the inspection's attributes available directly on car, because you've convinced ActiveRecord with a join to return two records of one class as a single row. Rails will tell you its class is Car, but it's more than a Car. You'll either get each Car once, for no matching Inspections, or multiple times for each matching Inspection.

Answer 4

This should work:

Car.includes(:inspections).where( inspections: { month: '2013-04-01' })