问题描述
在没有插值的情况下重写此查询的最佳方法是什么?
What would be the best way of rewriting this query without interpolation?
def case_joins(type)
subquery = <<-SQL.squish
SELECT id FROM cases c2
WHERE c2.title_id = titles.id AND c2.value = 0 AND c2.type = '#{type}'
ORDER BY c2.created_at DESC LIMIT 1
SQL
"LEFT OUTER JOIN cases ON cases.title_id = titles.id AND cases.value = 0 AND cases.type = '#{type}' AND cases.id = (#{subquery})"
end
推荐答案
我假设您想避免变量插值,因为它对 SQL 注入开放,因此很危险.我会简单地加入从子查询中选择的案例,而不是将子查询放入 WHERE
条件中.这确实涉及插值,但仅限于 AR 生成的 SQL.我还将它作为一个作用域来实现,以利用 AR 作用域链:
I'm assuming that you want to avoid interpolation of variables, which is dangerous since its open to SQL injection. I would simply join onto the cases selected from the subquery instead of putting the subquery into the WHERE
conditions. This does involve interpolation, but only of AR-generated SQL. I would also implement it as a scope to leverage AR scope chaining:
class Title < ActiveRecord::Base
def self.case_joins(type)
case_query = Case.from("cases c").where(c: {title_id: title_id, value: 0, type: type}).order('c.created_at DESC').limit(1)
joins("LEFT OUTER JOIN (#{case_query.to_sql}) cases ON cases.title_id = titles.id")
end
end
这样,您可以像这样将范围链接到其他人:
This way, you can chain the scope to others like so:
Title.where(attribute1: value1).case_joins("typeA")
(注意删除了外部 SELECT
中多余的 WHERE
条件.)
(Note that removed the superfluous WHERE
conditions in the outer SELECT
.)
这篇关于原始 SQL 请求中的 Rails 4 字符串插值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!