CODEWITHSUNDEEP
ruby-on-railspostgresqlleft-join
legendary_rob
legendary_rob

Reputation: 13012

Rails 5 left_outer_join with specific saved ID

I have a system with the following models 

class Application < ApplicationRecord
  has_many :offers, dependent: :destroy
  belongs_to :accepted_offer, class_name: 'Offer', 
                              foreign_key: 'accepted_offer_id',
                              optional: true

class Offer < ApplicationRecord
  belongs_to :application

and was creating a report that gathers all the offers by accepted_offer_id like thus 

Application.find_each do |app|
  offer = Offer.find(app.accepted_offer_id) if app.accepted_offer_id
  report.push(report_body(app, offer))
end

This is becoming too slow, I would like to rewrite the report bit so that it leverages a left join to make the connection with a sinle query to the db.

I would like to have the query return all offers that are stored as accepted_offer_id on the applications table.

Offer.left_outer_joins(:applications).where(id: { 'application.accetped_offer_id' })

I know the above is wrong but I am sure it must be possible to get the collection with a single query?

Upvotes: 0

Views: 30

Answers (3)

3limin4t0r
3limin4t0r

Reputation: 21120

You could also choose to use a sub-query.

offer_ids = Application.select(:accepted_offer_id)
offers = Offer.where(id: offer_ids)

Should result in (MySQL):

SELECT `offers`.*
FROM `offers`
WHERE `offers`.`id` IN (
  SELECT `applications`.`accepted_offer_id`
  FROM `applications`
)

Upvotes: 0

Mark
Mark

Reputation: 6445

To rewrite the first bit a bit nicer:

Application.find_each do |app|
  report.push(report_body(app, app.accepted_offer)) if app.accepted_offer
end

Moving onto rewriting the SQL, I'm pretty sure what you need to do is:

Offer.joins(:application).where('offers.id = applications.accepted_offer_id')

Upvotes: 1

Ursus
Ursus

Reputation: 30056

Anything like this?

class Offer < ApplicationRecord
  belongs_to :application

  scope :accepted, -> { joins(:application).where('offers.id = applications.accepted_offer_id') } 
end

Offer.accepted

Upvotes: 2

Related Questions