Arel：使用符号的左外部联接 [英] Arel: Left outer join using symbols

问题描述

我有一个用例，其中我从某个模型获得了符号化的深层关联，并且我必须执行某些涉及使用外部联接的查询。没有手工编写完整的SQL怎么办？

I have this use case where I get the symbolized deep associations from a certain model, and I have to perform certain queries that involve using outer joins. How can one do it WITHOUT resorting to write the full SQL by hand?

我不想要的答案：
-使用include（并不能很好地解决深层关联（.includes（：cars => [：windows， ：engine => [：ignition] .....意外工作），我不希望其副作用）
-自己编写SQL（对不起，现在是2013年，跨数据库支持，等等）。 ..，而我获取的对象是只读的，还有更多副作用）

Answers I don't want: - using includes (doesn't solve deep associations very well ( .includes(:cars => [:windows, :engine => [:ignition]..... works unexpectedly ) and I don't want its side-effects) - writing the SQL myself (sorry, it's 2013, cross-db support, etc etc..., and the objects I fetch are read_only, more side-effects)

我想要一个Arel解决方案。我知道使用模型中的arel_table我可以构造SQL表达式，联接中也有一个DSL，但是不知何故我无法在模型的joins方法中使用它：

I'd like to have an Arel solution. I know that using the arel_table's from the models I can construct SQL expressions, there's also a DSL for the joins, but somehow i cannot use it in the joins method from the model:

car = Car.arel_table
engine = Engine.arel_table

eng_exp = car.join(engine).on(car[:engine_id].eq(engine[:id]))
eng_exp.to_sql #=> GOOD! very nice!
Car.joins(eng_exp) #=> Breaks!!

为什么这不起作用超出了我的范围，我不知道到底缺少什么，但这是我现在拥有的最接近解决方案的东西。身体可以帮助我完成我的示例，或为我提供一个不错的解决方法，或者告诉我Rails何时包括如此明显必要的功能，将由衷的感谢。

Why this doesn't work is beyond me. I don't know exactly what is missing. But it's the closest thing to a solution I have now. If somebody could help me completing my example or provide me with a nice work-around or tell me when will Rails include such an obviously necessary feature will have my everlasting gratitude.

推荐答案

我发现了一个博客文章，旨在解决此问题： http://blog.donwilson.net/2011/11/constructing-a -比简单的带有轨道和护栏的查询/

I found a blog post that purports to address this problem: http://blog.donwilson.net/2011/11/constructing-a-less-than-simple-query-with-rails-and-arel/

基于此（和我自己的测试），以下方法应该可以工作针对您的情况：

Based on this (and my own testing), the following should work for your situation:

car = Car.arel_table
engine = Engine.arel_table   

sql = car.project(car[Arel.star])
        .join(engine, Arel::Nodes::OuterJoin).on(car[:engine_id].eq(engine[:id]))

Car.find_by_sql(sql)

这篇关于Arel：使用符号的左外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！