Find_by_sql作为Rails范围 [英] Find_by_sql as a Rails scope
问题描述
Sitepoint的r937足以帮助我确定需要从数据库返回正确结果的查询.
r937 from Sitepoint was kind enough to help me figure out the query I need to return correct results from my database.
我需要的是能够将此查询用作作用域,并能够将其他作用域链接到该作用域.
What I need is to be able to use this query as a scope and to be able to chain other scopes onto this one.
查询是:
SELECT coasters.*
FROM (
SELECT order_ridden,
MAX(version) AS max_version
FROM coasters
GROUP BY order_ridden
) AS m
INNER JOIN coasters
ON coasters.order_ridden = m.order_ridden
AND COALESCE(coasters.version,0) = COALESCE(m.max_version,0)
我尝试过像这样做一个示波器:
I tried making a scope like so:
scope :uniques, lambda {
find_by_sql('SELECT coasters.*
FROM (
SELECT order_ridden,
MAX(version) AS max_version
FROM coasters
GROUP BY order_ridden
) AS m
INNER JOIN coasters
ON coasters.order_ridden = m.order_ridden
AND COALESCE(coasters.version,0) = COALESCE(m.max_version,0)')
}
但是当我尝试将另一个示波器链接到它时,它失败了.有没有办法像正常作用域一样运行此查询?
But when I tried chaining another one of my scopes onto it, it failed. Is there a way I can run this query like a normal scope?
推荐答案
find_by_sql
返回Array
.但是您需要ActiveRecord::Relation
链接其他作用域.
find_by_sql
returns an Array
. But you need an ActiveRecord::Relation
to chain additional scopes.
使用将返回ActiveRecord::Relation
的ActiveRecord方法重写查询的一种方法是将其重新排列一点,以使嵌套发生在INNER JOIN
部分中.
One way to rewrite your query using ActiveRecord methods that will return an ActiveRecord::Relation
would be to rearrange it a little bit so that the nesting happens in the INNER JOIN
portion.
您可能想尝试类似的东西:
You may want to try something like:
scope :uniques, lambda {
max_rows = select("order_ridden, MAX(version) AS max_version").group(:order_ridden)
joins("INNER JOIN (#{max_rows.to_sql}) AS m
ON coasters.order_ridden = m.order_ridden
AND COALESCE(coasters.version,0) = COALESCE(m.max_version,0)")
}
这篇关于Find_by_sql作为Rails范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!