Rails 4原始SQL请求中的字符串插入

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

3 个解决方案

#1

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:

我假设您希望避免变量的插值，这是危险的，因为它对SQL注入是开放的。我只需将子查询加入到从子查询中选择的用例中，而不是将子查询放入WHERE条件中。这确实需要插值，但仅限于ar生成的SQL。我还将把它作为一种利用AR范围链接的范围来实施:

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")

(Note that removed the superfluous WHERE conditions in the outer SELECT.)

(注意，在外部选择的条件下去掉多余的部分。)

#2

It's difficult to infer what the rest of your code looks like, but I presume titles is being used in a query further up your call stack.

很难推断出代码的其余部分是什么样子的，但我认为标题正在调用堆栈的更上层的查询中使用。

If you were to use ActiveRecord instead of native SQL, you could do something like this:

如果您要使用ActiveRecord而不是原生SQL，您可以这样做:

def case_joins(scope, title_id, type)
  ids = Case.where(title_id: title_id, value: 0, type: type)
             .order('created_at desc').limit(1).pluck(:id)

  scope.joins('left outer join cases on cases.title_id = titles.id')
             .where(value: 0, type: type, id: ids)
end

scope here is the current AR query you are modifying.

这里的范围是您正在修改的当前AR查询。

This is off the top of my head, so I'm not sure if the AR syntax above is correct, but it does avoid the need to interpolate SQL and also uses scoping.

这超出了我的理解范围，因此我不确定上面的AR语法是否正确，但它确实避免了插入SQL并使用范围。

To be honest, though, it's not all that much more readable than native SQL, and so YMMV. It does at least mean that (apart from the join) you're not encoding SQL in your code.

不过，老实说，它并不比原生SQL和YMMV更具有可读性。它至少意味着(除了join)在代码中没有编码SQL。

#3

Here is modification of @eirikir's answer, that works the same way as method in question.

这里是对@eirikir的回答的修改，它与问题的方法是一样的。

def case_joins(type)
  case_query = Case.from("cases c").where('c.title_id = titles.id AND c.value = 0 AND c.type = ?', type).order('c.created_at DESC').select(:id).limit(1)
 "LEFT OUTER JOIN cases ON cases.title_id = titles.id AND cases.id = (#{case_query.to_sql})"
end

#1

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