Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ActiveRecord: Adding condition to ON clause for includes

I have a model offers and another historical_offers, one offer has_many historical_offers.

Now I would like to eager load the historical_offers of one given day for a set of offers, if it exists. For this, I think I need to pass the day to the ON clause, not the WHERE clause, so that I get all offers, also when there is no historical_offer for the given day.

With Offer.where(several_complex_conditions).includes(:historical_offers).where("historical_offers.day = ?", Date.today)

I would get

SELECT * FROM offers 
LEFT OUTER JOIN historical_offers 
ON offers.id = historical_offers.offer_id 
WHERE day = '2012-11-09' AND ...

But I want to have the condition in the ON clause, not in the WHERE clause:

SELECT * FROM offers 
LEFT OUTER JOIN historical_offers 
ON offers.id = historical_offers.offer_id AND day = '2012-11-09' 
WHERE ...

I guess I could alter the has_many definition with a lambda condition for a specific date, but how would I pass in a date then?

Alternatively I could write the joins mysqlf like this:

Offer.where(several_complex_conditions)
  .joins(["historical_offers ON offers.id = historical_offers.offer_id AND day = ?", Date.today])

But how can I hook this up so that eager loading is done?

like image 746
Jan Avatar asked Nov 09 '12 11:11

Jan


1 Answers

After a few hours headscratching and trying all sorts of ways to accomplish eager loading of a constrained set of associated records I came across @dbenhur's answer in this thread which works fine for me - however the condition isn't something I'm passing in (it's a date relative to Date.today). Basically it is creating an association with the conditions I wanted to put into the LEFT JOIN ON clause into the has_many condition.

has_many :prices, order: "rate_date"
has_many :future_valid_prices,
    class_name: 'Price',
    conditions: ['rate_date > ? and rate is not null', Date.today-7.days]

And then in my controller:

@property = current_agent.properties.includes(:future_valid_prices).find_by_id(params[:id])
like image 153
edralph Avatar answered Oct 20 '22 18:10

edralph