Sitepoint 的 r937 非常友善地帮助我找出从数据库返回正确结果所需的查询。
我需要的是能够使用这个查询作为范围,并且能够将其他范围链接到这个范围上。
查询是:
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)
我尝试制作一个像这样的范围:
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)')
}
但当我尝试将另一台瞄准镜链接到它上面时,它失败了。有没有办法可以像正常范围一样运行这个查询?
find_by_sql
返回一个Array
。但你需要一个ActiveRecord::Relation
链接额外的范围。
使用 ActiveRecord 方法重写查询的一种方法将返回ActiveRecord::Relation
就是稍微重新排列一下,以便嵌套发生在INNER JOIN
部分。
您可能想尝试以下操作:
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)")
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)