问题描述
我有一个用例,其中我从某个模型获得了符号化的深层关联,并且我必须执行某些涉及使用外部联接的查询。没有手工编写完整的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.
推荐答案
我发现了一个博客文章,旨在解决此问题:
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:使用符号的左外部联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!