---
title: Using Arel to Compose SQL Queries
teaser: |
  Use composition to build more complex SQL queries in Ruby
  using ActiveRelation's API.
tags: web,rails
author: Sage Griffin
published_on: 2014-02-17
---

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:

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

It also handles nils:

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

It handles arrays:

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

It even handles arrays containing nil!

```ruby
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`][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 <abbr title="Structured Query Language">SQL</abbr> string literal.
However, there's a better way.

## Arel

[Arel][arel] is a library that was introduced in Rails 3 for use in constructing
<abbr title="Structured Query Language">SQL</abbr> queries. Every time you pass
a hash to where, it goes through Arel eventually. Rails exposes this with a
public <abbr title="Application Programming Interface">API</abbr> that we can
hook into when we need to build a more complex query.

Let's look at an example:

```ruby
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 <abbr
title="Structured Query Language">SQL</abbr> 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 <abbr title="Structured Query Language">SQL</abbr> 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.

```ruby
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 `Node`s in the file
[predications.rb][predications].

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:

```ruby
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][science].

[arel]: https://github.com/rails/arel
[predications]: https://github.com/rails/arel/blob/master/lib/arel/predications.rb
[where-not]: https://thoughtbot.com/blog/activerecords-wherenot
[science]: http://rubyscience.com