精炼时使用内部联接包括的Act​​iveRecord [英] Refining the inner join when using includes in ActiveRecord

问题描述

我如何添加一个条件，在活动记录由产生的ON子句包括同时保持预先加载？

让我们说我有这些类：

 类车
   的has_many：检查
结束

一流的检验
   belongs_to的：汽车
结束
 

现在我能做的：

  Car.includes（：检查）

SELECT * FROM汽车LEFT OUTER JOIN视察cars.id = inspections.car_id
 

不过，我要生成SQL语句：

  SELECT * FROM汽车LEFT OUTER JOIN视察cars.id = inspections.car_id
    与inspections.month ='2013年4月1日
 

（这不工作）：

  Car.includes（：检查）。凡（inspections.month = 2013年4月1日）

SELECT * FROM汽车LEFT OUTER JOIN视察cars.id = inspections.car_id
    WHERE inspections.month ='2013年4月1日
 

解决方案

我已经更广泛地重新思考你的问题。我想你正面临一个code设计问题，以及一个ActiveRecord查询的问题（而不是？）。

您问返回轿车上 .inspections 已被重新定义为那些符合某一特定日期检查的关系。 ActiveRecord的不允许重新定义一个模型关联的飞行，基于查询。

如果你没有要求的检验日期的动态条件下，我会告诉你使用的has_many：通过与：条件。

 的has_many：passed_inspections，：通过=＆GT; ：检查，：条件=＆GT; {：通过=＆GT;真正}

@cars = Cars.includes（：passed_inspections）
 

显然，这不，如果你需要提供一个检验日期在飞行工作。

所以，在最后，我会告诉你做这样的事情：

  @cars = Cars.all
@inspections = Inspection.where（检查：{月：2013年4月1日，car_id：@ cars.pluck（：ID）}）
 

（确切的说，那最好的实施 car_id WHERE条件了争议。而你则需要组 @inspections 按 car_id 来得到一个特定的时刻正确的子集。）

另外，在生产环境中，你也许能够依靠一些相当不错的/聪明的ActiveRecord的缓存。我不能肯定这一点。

 高清inspections_dated（月）
  inspections.where（月：月）
结束
Car.includes（：检查）。每个{|汽车| car.inspections_dated（月）.each.etc。 }
 

另外，交替

您可以通过手动SQL，招ActiveRecord的进入给你延长不明接口租车对象：

  @cars_with_insp = Car.join（LEFT OUTER JOIN视察inspections.car_id = cars.id和inspections.month ='2013年4月1日）。选择（汽车，*，检查。*）
@ cars_with_insp.each {| C |把c.name;把c.inspection_month}
 

您会看到，在。每次，你必须直接对车检查的属性，因为你相信ActiveRecord的一个连接到两个记录一类为返回单行。 Rails会告诉你它的类是汽车，但它不仅仅是一辆汽车。你要么得到每辆汽车一次，没有匹配检查，或为每个匹配检查多次。

How do I add a condition to the ON clause generated by includes in active record while retaining eager loading?

Let's say I have these classes:

class Car
   has_many :inspections
end

class Inspection
   belongs_to :car
end

Now I can do:

Car.includes(:inspections)

Select * from cars LEFT OUTER JOIN inspections ON cars.id = inspections.car_id

But I want to generate this sql:

Select * from cars LEFT OUTER JOIN inspections ON cars.id = inspections.car_id 
    AND inspections.month = '2013-04-01'

(this doesn't work):

Car.includes(:inspections).where("inspections.month = 2013-04-01")

Select * from cars LEFT OUTER JOIN inspections ON cars.id = inspections.car_id
    WHERE inspections.month = '2013-04-01'

解决方案

I've rethought your question more broadly. I think you are facing a code design problem as well as (instead of?) an ActiveRecord query problem.

You are asking to return a relation of Cars on which .inspections has been redefined to mean those Inspections matching a specific date. ActiveRecord does not allow you to redefine a model association on the fly, based on a query.

If you were not asking for a dynamic condition on the inspection date, I would tell you to use a has_many :through with a :condition.

has_many :passed_inspections, :through => :inspections, :conditions => {:passed => true}

@cars = Cars.includes(:passed_inspections)

Obviously, that would not work if you need to supply an inspection date on the fly.

So, in the end, I would tell you to do something like this:

@cars = Cars.all
@inspections = Inspection.where(inspections: {month: '2013-04-01', car_id: @cars.pluck(:id)})

(Exact, best implementation of that car_id where condition is up to debate. And you'll then need to group the @inspections by car_id to get the right subset in a given moment.)

Alternately, in a production environment, you might be able to rely on some fairly good/clever ActiveRecord caching. I'm not certain of this.

def inspections_dated(month)
  inspections.where(month: month)
end
Car.includes(:inspections).each{|car| car.inspections_dated(month).each.etc. }

Alternately, Alternately

You can, through manual SQL, trick ActiveRecord into giving you extended Car objects with an unclear interface:

@cars_with_insp = Car.join("LEFT OUTER JOIN inspections ON inspections.car_id = cars.id AND inspections.month = '2013-04-01'").select("cars.*, inspections.*")
@cars_with_insp.each{|c| puts c.name; puts c.inspection_month}

You'll see, in that .each, that you have the inspection's attributes available directly on car, because you've convinced ActiveRecord with a join to return two records of one class as a single row. Rails will tell you its class is Car, but it's more than a Car. You'll either get each Car once, for no matching Inspections, or multiple times for each matching Inspection.

这篇关于精炼时使用内部联接包括的Act​​iveRecord的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！