CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordmodelactive-record-query
gosseti
gosseti

Reputation: 975

Rails ActiveRecord model scope with joins on has_many associations

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?

Upvotes: 2

Views: 7251

Answers (2)

randallreedjr
randallreedjr

Reputation: 340

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

Upvotes: 4

a14m
a14m

Reputation: 8055

Rails 5 introduced left_outer_joins method that can be used

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

Upvotes: 1

Related Questions