ActiveRecord的：添加条件ON子句包括 [英] ActiveRecord: Adding condition to ON clause for includes

问题描述

我有一个模型，提供和其他historical_offers，一个提供的has_many historical_offers。

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

现在我想急于装入一张一天的historical_offers一组提供的，如果它存在。对于这一点，我想我需要一天传递给ON子句，而不是WHERE子句，让我得到的所有报价，也当没有historical_offer对于给定的一天。

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.

使用     Offer.where（several_complex_conditions）.includes（：historical_offers）。凡（？historical_offers.day =，Date.today）

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

我会得到

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

不过，我想具备的条件在ON子句中，而不是在WHERE子句：

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 ...

我想我可以改变一个拉姆达条件的has_many定义的具体日期，但如何将我传递一个日期呢？

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?

另外，我可以写的加入mysqlf是这样的：

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?

推荐答案

在几个小时headscratching和尝试各种方法来完成一组受限的相关记录我碰到@ dbenhur的的回答在这个线程这对我来说工作正常 - 但条件是不是我敢传递（这是相对于Date.today日期）。基本上，它是建立与我想放进左边的条件协会加入有关条款列入的has_many条件。

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])

这篇关于ActiveRecord的：添加条件ON子句包括的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！