Using arel_table for ILIKE (yes, even with integers)

You've probably been there - you want to use a search input to search for records on multiple columns, and you're not quite convinced that you need the big guns like Sphinx for this job. Searching using only ActiveRecord is too limited, so you reach for a SQL string inside a class method or named scope.

scope :for_code_or_title_like, lambda { |search_term|
  where(
    <<-SQL
      title ILIKE '%#{search_term}%' OR
      cast(code as text) ILIKE '%#{search_term}'
    SQL
  )
}

You feel pretty proud of yourself for now, until a buddy points out that you're vulnerable to injection attacks this way. The last thing in the world you want to do is parse and clean the input. This stuff should just work, right? So you go searching for another way. And finally, arel_table comes to the rescue.

This has two wonderful tools that that are going to save our butts today: the .matches method gives us ILIKE functionality, and Arel::Nodes::NamedFunction allows us to perform SQL functions on our columns. Let's see them in action:

scope :for_code_or_title_like, lambda { |search_term|
  code = Arel::Nodes::NamedFunction.new(
    "CAST",
    [arel_table[:code].as("TEXT")]
  )

  where(
    code.matches(
      "%#{search_term}%"
    ).or(
      arel_table[:title].matches(
        "%#{search_term}%"
      )
    )
  ).order(arel_table[:code])
}

This just saved you tons of headache, so you crack a celebratory brew and call it a day. Cheers!