Using Arel to Compose SQL Queries

Rails gives us a great DSL for constructing most queries. With its knowledge of the relationships between our tables, it’s able to construct join clauses with nothing more than the name of a table. It even aliases your tables automatically!

The method we most often reach for when querying the database is the where method. 80% of the time, your query will only be checking equality, which is what where handles. where is smart. It handles the obvious case:

where(foo: 'bar') # => WHERE foo = 'bar'

It also handles nils:

where(foo: nil) # => WHERE foo IS NULL

It handles arrays:

where(foo: ['bar', 'baz']) # => WHERE foo IN ('bar', 'baz')

It even handles arrays containing nil!

where(foo: ['bar', 'baz', nil]) # => (WHERE foo IN ('bar', 'baz') OR foo IS NULL)

With Rails 4, we can also query for inequality by using where.not. However, where has its limitations. It can only combine statements using AND. It doesn’t provide a DSL for comparison operators other than = and <>.

When faced with a query that requires an OR statement, or when needing to do numeric comparisons such as <=, many Rails developers will reach for writing out a SQL string literal. However, there’s a better way.

Arel

Arel is a library that was introduced in Rails 3 for use in constructing SQL queries. Every time you pass a hash to where, it goes through Arel eventually. Rails exposes this with a public API that we can hook into when we need to build a more complex query.

Let’s look at an example:

class ProfileGroupMemberships < Struct.new(:user, :visitor)
  def groups
    @groups ||= user.groups.where("private = false OR id IN ?", visitor.group_ids)
  end
end

When we decide which groups to display on a user’s profile, we have the following restriction. The visitor may only see the group listed if the group is public, or if both users are members of the group. Even for a minor query like this, there are several reasons we would want to avoid using SQL string literals here.

• Abstraction/Reuse
  • If we wanted to reuse any piece of this query, we would end up with a leaky abstraction at best involving string interpolation.
• Readability
  • As complex SQL queries grow, they can quickly become difficult to reason about. Since they’re so difficult to break apart, the reader often has to understand the entire query to understand any individual part.
• Reliability
  • If we join to another table, our query will immediately break due to the ambiguity of the id column. Even if we qualify the columns with the table name, this will break as well if Rails decides to alias the table name.
• Repetition
  • Often times we end up rewriting code that we already have as a scope on the class, just to be able to use it with an OR statement.

Refactoring to use Arel

The method Rails provides to access the underlying Arel interface is called arel_table. If you’re working with another class’s table, the code may become more readable if you assign a local variable or create a method to access the table.

def table
  Group.arel_table
end

The Arel::Table object acts like a hash which contains each column on the table. The columns given by Arel are a type of Node, which means it has several methods available on it to construct queries. You can find a list of most of the methods available on Nodes in the file predications.rb.

When breaking apart a query to use Arel, I find a good rule of thumb is to break out a method anywhere the word AND or OR is used, or when something is wrapped in parenthesis. Keeping this rule in mind, we end up with the following:

class ProfileGroupMemberships < Struct.new(:user, :visitor)
  def groups
    @groups ||= user.groups.where(public.or(shared_membership))
  end

  private

  def public
    table[:private].eq(false)
  end

  def shared_membership
    table[:id].in(visitor.group_ids)
  end

  def table
    Group.arel_table
  end
end

The resulting code is slightly more verbose due to Arel’s interface, but we’ve given intention-revealing names to the underlying pieces, and are able to compose them in a satisfying fashion. The body of our public groups method now also describes the business logic we want, as opposed to how it is implemented.

With more complex queries, this can go a long way towards being able to easily reason about what a query is accomplishing, as well as debugging individual pieces. It also becomes possible to reuse pieces of scopes with OR clauses, or in the body of JOIN ON statements.

What’s next

• Learn more about composition over inheritance in Ruby with Ruby Science.