Rails ActiveRecord model scope with joins on has_many associations

Question

I’m currently setting up a scope in my Rails model to be used by ActiveAdmin. The scope I want to build should find every Job that has a survey_date in the past, with a Job.survey present, and no Job.quotes present.

Here is an abbreviated version of my Job model:

has_many :quotes
has_many :surveys

scope :awaiting_quote, lambda { joins(:surveys, :quotes).where('survey_date < :current_time AND surveys.id IS NOT NULL AND quotes.id IS NULL', { current_time: Time.current }) }

How should I change my scope so that it correctly finds the revelant Job records?

Please note `joins` will create an INNER JOIN which means that `quotes.id IS NULL` has no impact and these jobs will never show up try `includes(:surveys,:quotes).where('survey_date < :current_time AND surveys.id IS NOT NULL AND quotes.id IS NULL', { current_time: Time.current })` this will create 2 LEFT OUTER JOIN s and may function better for you. Here is a great [article](http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html) on association loading and how the methods work — engineersmnky, May 19 '15 at 17:12

randallreedjr · Answer 1 · 2018-06-14T21:42:41.557

Update for Rails 5

As mad_raz mentions, in Rails 5.0+, you can use left_outer_joins:

scope :awaiting_quote, -> { joins(:surveys).
  left_outer_joins(:quotes).
  where('survey_date < :current_time', { current_time: Time.current }).
  where('quotes.id IS NULL')
}

However, you must still provide a where('quotes.id IS NULL') check to only return those records that have not yet received a quote. See https://stackoverflow.com/a/16598900/1951290 for a great visual representation of outer joins.

It still probably makes the most sense to split these into two separate scopes.

Rails 4

You can create left outer joins using joins, you just have to be a bit more explicit.

scope :awaiting_quote, -> { joins(:surveys).
  joins('LEFT OUTER JOIN quotes ON quotes.job_id = jobs.id').
  where('survey_date < :current_time', { current_time: Time.current }).
  where('quotes.id IS NULL')
}

You don't need surveys.id IS NOT NULL since a successful inner join will not include nil ids.

It probably makes more sense to split these into two separate scopes, :has_survey and :without_quote, which can then be combined into a method.

def self.awaiting_quote
  Job.has_survey.without_quote
end

score 1 · Accepted Answer · answered Jun 01 '18 at 08:19

1

Rails 5 introduced left_outer_joins method that can be used

scope :awaiting_quote, -> { joins(:surveys).left_outer_joins(:quotes).where('yada yada') }

answered Jun 01 '18 at 08:19

a14m

7,808
8
50
67

Rails ActiveRecord model scope with joins on has_many associations

2 Answers2