#355 Hacking with Arel pro

At some point when building a Rails application we’ll probably need to write a complex SQL query like the one we have below for searching for products.

class Product < ActiveRecord::Base
  belongs_to :category
  attr_accessible :name, :price, :category_id, :released_at, :discontinued_at, :stock

  def self.search(query)
    where("released_at <= ? and (discontinued_at is null or discontinued_at > ?) and stock >= ? and name like ?", Time.zone.now, Time.zone.now, 2, "%#{query}%")
  end
end

This query searches for products that are released, that aren’t discontinued, that are in stock and which have a name that matches the search term. There are various third-party libraries that can help with queries like this; one such is Squeel which was covered in the last episode. In this episode we’ll show you various ways that we can rewrite this query using only ActiveRecord and Arel.

Improving Our Query

A query with a large number of arguments at the end and question marks scattered throughout the SQL can be difficult to read. One solution to this problem is to create a hash of the parameters. This has the added advantage that we can remove duplicate values, like the two calls to Time.zone.now we have in our query. We can then replace the question marks in the query with the names of the hash’s keys.

def self.search(query)
  where("released_at <= :now and (discontinued_at is null or discontinued_at > :now) and stock >= :stock and name like :query", now: Time.zone.now, stock: 2, query: "%#{query}%")
end

Another option is to move sections of the query into named scopes. This has a nice side-effect of making the sections reusable elsewhere in the application.

class Product < ActiveRecord::Base
  belongs_to :category
  attr_accessible :name, :price, :category_id, :released_at, :discontinued_at, :stock
  
  scope :released,          -> { where("released_at <= ?", Time.zone.now) }
  scope :not_discontinued,  -> { where("discontinued_at is null or discontinued_at > ?", Time.zone.now) }
  scope :in_stock,          -> { where("stock >= ?", 2) }
  scope :available,         -> { released.not_discontinued.in_stock }
  scope :search.            -> { available.where("name like ?", "%#{query}%") }
end

We now have five different named scopes that describe the different parts of our search query. What’s nice about this approach is that it’s self-documenting as each scope has a name that describes what it does. Note that we have an available scope that is made up from other scopes and a search scope which uses available to find products that match the query.

We’re using the Ruby 1.9 lambda syntax (->) here. This is the same as using lambda but is a little shorter to type, although arguments are defined differently, outside the block. You can see this in the search scope above. It’s not necessary to use a lambda in every one of these scopes. For example the in_stock scope above doesn’t deal with the current time like the other scopes do so we could remove the lambda here and have the scope be evaluated at the class level. It seems, though, that omitting the lambda will be deprecated in Rails 4. The reason for this is that it can be easy to introduce bugs when the lambda is omitted. For example our available scope doesn’t appear to use the current time so it looks like we might be able to skip the lambda. We can’t, though, as some of the scopes it calls do use the current time. When in doubt, always use a lambda.

An alternative to using the scope method is to define class methods. This can be preferable, especially when a scope takes arguments, or is long. We could replace our search scope with a class method that looks like this:

def self.search(query)
  available.where("name like ?", "%#{query}%")
end

This has the same effect. We can do anything in a class method that we can do in a scope.

Using Arel

Scopes can only take us so far. We’re still writing raw SQL code and it’s important to remember that different databases have slightly different syntax. If we were to switch this application to use Postgres the search query above would be case-sensitive and we’d need to use ilike to make the it case-insensitive. Differences like this can cause problems and are easy to overlook and this is where Arel comes in handy. ActiveRecord uses this under the hood to generate its SQL but it has a lot of features that ActiveRecord doesn’t provide an interface for. We’re interested in the predicates that Arel provides. We can use less than (lt) and greater than (gt) methods and more to compare columns to various values. Fortunately it’s easy to Arel directly through ActiveRecord and we’ll demonstrate this in the console.

> t = Product.arel_table
 => #<Arel::Table:0x007f90a4439cc0 @name="products", @engine=Product(id: integer, name: string, category_id: integer, price: decimal, released_at: datetime, discontinued_at: datetime, stock: integer, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil> 
 > t[:price].lt(10)
 => #<Arel::Nodes::LessThan:0x007f90a2ebaca0 @left=#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0x007f90a4439cc0 @name="products", @engine=Product(id: integer, name: string, category_id: integer, price: decimal, released_at: datetime, discontinued_at: datetime, stock: integer, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>, name=:price>, @right=10> 
> Product.where(_)
  Product Load (0.8ms)  SELECT "products".* FROM "products" WHERE ("products"."price" < 10)
# List of matching products omitted

We start here by getting the arel_table for the Product model. We can perform various actions on this table’s attributes and we’ve fetched the products with a price less than 10. This returns an Arel::Node and these can be passed directly in to a where call to return the matching products.

Arel doesn’t have extensive documentation so it’s up to us to browse the source code if we want to work out everything that it’s capable of. A good place to start is the predications file as this provides the various methods that we can call on an attribute, eq, not_eq, gt, lt etc, to figure out the different queries we can perform. For example its matches method generates a SQL LIKE condition, although that isn’t obvious from the source code here which just creates a new Nodes::Matches object.

def matches other
  Nodes::Matches.new self, other
end

Looking at the source code for this class won’t help either. Instead we need to look at the source code’s visitors directory where we’ll find the adaptors for the various types of database that are supported. If we look at the file for Postgres we’ll find a method called visit_Arel_Nodes_Matches and we can see that an ILIKE clause is used here for comparison.

module Arel
  module Visitors
    class PostgreSQL < Arel::Visitors::ToSql
      private

      def visit_Arel_Nodes_Matches o
        "#{visit o.left} ILIKE #{visit o.right}"
      end

      def visit_Arel_Nodes_DoesNotMatch o
        "#{visit o.left} NOT ILIKE #{visit o.right}"
      end

      def visit_Arel_Nodes_DistinctOn o
        "DISTINCT ON ( #{visit o.expr} )"
      end
    end
  end
end

This class inherits from an ToSql class and if we look at that we’ll see how the other nodes convert over to SQL. We can use this knowledge in the console to find all the products whose name matches a given value.

> Product.where(t[:name].matches("%catan%"))
  Product Load (0.4ms)  SELECT "products".* FROM "products" WHERE ("products"."name" LIKE '%catan%')
# List of matching products omitted

As we’re using SQLite as our database this query uses the LIKE operator but if it was running on Postgres it would have used ILIKE here.

We can now apply this to the search method in our model. Instead of using raw SQL here to find products by their name we can use the matches method instead.

def self.search(query)
  available.where(arel_table[:name].matches("%#{query}%"))
end

This is a simple change that gives us database-agnostic behaviour. We could apply this approach to the other SQL snippets in our scopes but it wouldn’t necessarily improve things as the Arel code can often be even less readable than the SQL. Where Arel really shines is when it acts as the engine for another interface. For example we could use Arel to generate some named scopes. For example we have a scope for finding products that have been released that uses a SQL snippet.

scope :released, -> { where("released_at <= ?", Time.zone.now) }

We’ll replace this with a new released_at_lt method.

scope :released, -> { released_at_lt(Time.zone.now) }

We’ll add this functionality in a new initializer file.

module ScopeGenerator
  def generate_scopes
    columns.each do |column|
      predicates_for(column).each do |predicate|
        scope "#{column.name}_#{predicate}", ->(value) do
          where(arel_table[column.name].send(predicate, value))
        end
      end
    end
  end

  def predicates_for(column)
    base = [:eq, :not_eq, :in, :not_in]
    case column.type
    when :integer, :float, :decimal, :date, :datetime, :timestamp, :time
      base + [:lt, :lteq, :gt, :gteq]
    when :string, :text
      base + [:matches, :does_not_match]
    else
      base
    end
  end
end

ActiveSupport.on_load :active_record do
  extend ScopeGenerator
end

Here we define a ScopeGenerator module and extend ActiveRecord::Base with its functionality when ActiveSupport loads. This means that the methods defined here will be class methods on ActiveRecord::Base and available to all models.

The generate_scopes method in this module generates scopes for each column in the table. For each column we call predicates_for, which is also defined here, to determine the predicates for each type of column. All column types get eq, not_eq, in and not_in but other types get more. Numeric and date/time fields get predicates for comparing values while string and text types get matching predicates. For each predicate we define a new scope based on the column and predicate names and perform a where clause on the model’s arel_table passing in the predicate and the value.

We can use our generate_scopes method in our Product model to generate the scopes relevant to the column type. We could improve this method so that we can pass in a list of the columns we want to generate scopes for and a list of the scopes to generate but the code we have will work and it will create the released_at_lt method we’re using in the Product model. We can use our new scopes to clean up our model by removing two of the scopes we created earlier and tidying up our search method.

class Product < ActiveRecord::Base
  belongs_to :category
  attr_accessible :name, :price, :category_id, :released_at, :discontinued_at, :stock
  
  generate_scopes
  
  scope :not_discontinued,  -> { where("discontinued_at is null or discontinued_at > ?", Time.zone.now) }
  scope :available,         -> { released_at_lteq(Time.zone.now).not_discontinued.stock_gte(2) }
  
  def self.search(query)
    available.name_matches("%#{query}%")
  end
end

One thing to be careful with when we use this approach is performance, especially if we have a lot of columns in a model as this will generate a large number of scopes. One way around this problem is to override method_missing and lazily generate the scopes only when they’re needed.

Handling OR Conditions

One area where this approach doesn’t help is in our not_discontinued scope. This has an OR condition to find the products where discontinued_at is either null or greater than the current time. Let’s see if it’s possible to create an OR condition on ActiveRecord::Relation objects so that we can write something like this:

scope :not_discontinued, -> { discontinued_at_eq(nil) | discontinued_at_gt(Time.zone.now) }

To do this is helps to have a better understanding of how Arel and ActiveRecord work together. For example if we have a query it will return an ActiveRelation object. If we call arel.class on this we’ll see that class that’s generated.

> Product.where(stock: 2).arel.class
 => Arel::SelectManager

This code generates an Arel::SelectManager. We can call constraints on this to return an array of Arel::Nodes which are the where conditions for this object.

> Product.where(stock: 2).arel.constraints
 => [#<Arel::Nodes::And:0x007f90a410b9e0 @children=[#<Arel::Nodes::Equality:0x007f90a410be18 @left=#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0x007f90a44307d8 @name="products", @engine=Product(id: integer, name: string, category_id: integer, price: decimal, released_at: datetime, discontinued_at: datetime, stock: integer, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>, name=:stock>, @right=2>]>]

This means that we can take a node from this constraint and call or on it and pass it another node.

 > Product.where(stock: 2).arel.constraints.first.or(Product.where(id: 1).arel.constraints.first)

This returns a new node which is an OR condition of these two clauses. We can call Product.where and pass in this new node to create a SQL query that check that either of the conditions is true.

> Product.where(stock: 2).arel.constraints.first.or(Product.where(id: 1).arel.constraints.first)
> Product.where(_)
  Product Load (104.3ms)  SELECT "products".* FROM "products" WHERE (("products"."stock" = 2 OR "products"."id" = 1))
 => [#<Product id: 1, name: "Settlers of Catan", category_id: 2, price: #<BigDecimal:7f90a2f7fa00,'0.3495E2',18(45)>, released_at: "2012-03-15 18:58:17", discontinued_at: nil, stock: 5, created_at: "2012-06-09 18:58:17", updated_at: "2012-06-09 18:58:17">]

With this concept in mind we’ll generate an initializer file called scope_operators and create an OR operator here.

module ScopeOperators
  def or(other)
    left = arel.constraints.reduce(:and)
    right = other.arel.constraints.reduce(:and)
    scope = merge(other)
    scope.where_values = [left.or(right)]
    scope
  end
  alias_method :|, :or
end

ActiveSupport.on_load :active_record do
  ActiveRecord::Relation.send(:include, ScopeOperators)
end

This is a little more complex than what we did in the console because it needs to take into consideration different scenarios. Here we define a ScopeOperators module and load it by including it on ActiveRecord::Relation when ActiveRecord::Base loads. This means that our or method will be available on all relation objects and we’ve aliased it as a pipe (|) method too. In it we call arel.constraints like we did in the console. This returns an array with potentially multiple constraints so we need to merge them together which we do by calling reduce(:and) on them. This will take the first constraint and call and passing the second then call and on that and pass it the third and so on. We do the same thing for the right side, which is the other scope that’s passed in, then merge the two scopes and override their where_values. This is a variable on ActiveRecord::Relation that we can override to change the nodes that are used by ActiveRecord. The normal behaviour for merge is to and the values together but we want to or them together here and that’s what we do when we override where_values.

Let’s try this out in the console to see if it works. We’ll search for the products whose stock is 1 or whose id is 1.

> Product.where(stock: 1) | Product.where(id: 1)
  Product Load (0.8ms)  SELECT "products".* FROM "products" WHERE (("products"."stock" = 1 OR "products"."id" = 1))
# List of matching products omitted

This produces the correct SQL query with an OR clause.

We can take this a little further and add more operators. We’ll add a not method which will do the same thing as the or method except that it calls not on the right side, and also an and method.

module ScopeOperators
  def or(other)
    left = arel.constraints.reduce(:and)
    right = other.arel.constraints.reduce(:and)
    scope = merge(other)
    scope.where_values = [left.or(right)]
    scope
  end
  alias_method :|, :or

  def not(other)
    left = arel.constraints.reduce(:and)
    right = other.arel.constraints.reduce(:and)
    scope = merge(other)
    scope.where_values = [left, right.not]
    scope
  end
  alias_method :-, :not

  def and(*args)
    merge(*args)
  end
  alias_method :&, :and
end

ActiveSupport.on_load :active_record do
  ActiveRecord::Relation.send(:include, ScopeOperators)
end

We can use these to merge queries in interesting ways. For example if we want to get the products whose id is between 1 and 5 but which isn’t 2 we can now do this:

> Product.where(id: 1..5) - Product.where(id: 2)

This will return the products with ids of 1, 3, 4 and 5.

A Match Method

We’ll finish this episode by showing one more approach for defining queries. This will work as an alternative to the scope generator we wrote earlier. Instead of having named scopes what if we could call where and pass in a hash? Rather than add this behaviour to the where method we’ll create a new match method. How might we do this?

We’ll implement this under another new initializer that we’ll call match_scope.

module MatchScope
  def match(matches)
    clause = matches.map do |attribute, conditions|
      Array.wrap(conditions).map do |predicates|
        predicates = predicates.kind_of?(Hash) ? predicates : {eq: predicates}
        predicates.map do |predicate, value|
          arel_table[attribute].send(predicate, value)
        end.reduce(:and)
      end.reduce(:or)
    end.reduce(:and)
    where(clause)
  end
end

ActiveSupport.on_load :active_record do
  extend MatchScope
end

This code defines the match method which is extended on ActiveRecord::Base and so which is available as a class method on all models. The code here is a little complex for one method but then end result calls where on a clause which is actually an Arel node. The code builds up the matches hash that’s passed in using various and and or combinations depending on what’s passed in as the options and builds up an Arel condition through that.

With this in place we can call match on any model passing in attributes and Arel conditions. This will generate a query matching that condition.

> Product.match(stock: {lt: 3})
  Product Load (0.7ms)  SELECT "products".* FROM "products" WHERE ("products"."stock" < 3)

It’s a little bit more complicated than this as it can handle other cases where there’s an array of values, like this which finds the products where the stock is either NULL or 3.

> Product.match(stock: [nil, {lt: 3}])
  Product Load (0.3ms)  SELECT "products".* FROM "products" WHERE (("products"."stock" IS NULL OR "products"."stock" < 3))

How might this affect our product search if we apply it here? We can now rewrite our search functionality like this.

class Product < ActiveRecord::Base
  belongs_to :category
  attr_accessible :name, :price, :category_id, :released_at, :discontinued_at, :stock
  
  def self.search(query)
    match(
      released_at: {lteq: Time.zone.now},
      discontinued_at: [nil, {gt: Time.zone.now}],
      stock: {gteq: 2},
      name: {matches: "%#{query}%"}
    )
  end
end

We now have a single search method with a call to match that handles all the search conditions. This is probably the cleanest way that we’re going to get for this particular query. Obviously the results in your own applications will vary depending on the queries it has to make.

Let’s try our new method out in the console.

> Product.search("catan")
  Product Load (0.4ms)  SELECT "products".* FROM "products" WHERE ("products"."released_at" <= '2012-06-10 22:18:55.948924' AND ("products"."discontinued_at" IS NULL OR "products"."discontinued_at" > '2012-06-10 22:18:55.948955') AND "products"."stock" >= 2 AND "products"."name" LIKE '%catan%')

A simple search term now generates a fairly complex query for the small hash of conditions that were passed in to search, but it returns the correct products.