Generate SQL with Arel the Ruby way!

As long as ActiveRecord::Base queries are simple, it is easy to stick to ActiveRecord::QueryMethods like:

Food.where name: 'Peanut butter'
# SELECT "foods".* FROM "foods"  WHERE "foods"."name" = 'Peanut butter'
=> [#<Food id: 1, name: "Peanut butter">]

But what about like-searching by several terms:

class Food < ActiveRecord::Base
  def self.search *terms
    conditions = terms.inject([]) { |conditions, word|
      conditions << "name LIKE ?"
    }.join(" OR ")
    where(conditions, *terms)
  end
end

and querying:

Food.search "Butter", "Apple"
=> [
  #<Food id: 1, name: "Peanut butter">
  #<Food id: 2, name: "Apple juice">
]

works, but is awkward because:

1. it deals with raw SQL fragments
2. confuses arguments with question marks
3. is by far not object oriented
4. suffers the lack of syntax check

The Food model scope can be refactored easily using Arel:

class Food < ActiveRecord::Base
  def self.search *terms
    conditions = terms.map { |term| 
      arel_table[:name].matches("%#{term}%") 
    }.inject(&:or)
    where(conditions)
  end 
end

The ActiveRecord::Core::ClassMethods#arel_table returns an Arel::Table object. It offers access to all model attributes. For a better understanding: it helps to send to_sql to Arel:

Food.arel_table[:name].matches("%Butter%").to_sql
=> "\"foods\".\"name\" LIKE '%Butter%'"

or the entire example conditions:

%w(Butter Apple).map { |term| 
  Food.arel_table[:name].matches("%#{term}%") 
}.inject(&:or).to_sql
=> "(\"foods\".\"name\" LIKE '%Butter%' OR \"foods\".\"name\" LIKE '%Apple%')"

which is awesome and reveals the power of Arel. No more writing SQL manually required.
It works:

Food.search "Butter", "Apple"
=> [
  #<Food id: 1, name: "Peanut butter">
  #<Food id: 2, name: "Apple juice">
]

The entire flexibility of Arel is documented at Arel API.
Further articles of interest:

• Scope the model!

Supported by Ruby 2.1.1 and Ruby on Rails 4.1.8