Pav31 Pav31 - 1 year ago 102
Ruby Question

Rails 4 string interpolation in raw SQL request

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 = AND c2.value = 0 AND c2.type = '#{type}'
ORDER BY c2.created_at DESC LIMIT 1
"LEFT OUTER JOIN cases ON cases.title_id = AND cases.value = 0 AND cases.type = '#{type}' AND = (#{subquery})"

Answer Source

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download