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
80% of the time, your query will only be checking equality, which is what
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 has its limitations. It can only
combine statements using
AND. It doesn’t provide a DSL for comparison
operators other than
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 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.
- If we wanted to reuse any piece of this query, we would end up with a leaky abstraction at best involving string interpolation.
- 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.
- If we join to another table, our query will immediately break due to the
ambiguity of the
idcolumn. Even if we qualify the columns with the table name, this will break as well if Rails decides to alias the table name.
- If we join to another table, our query will immediately break due to the ambiguity of the
- 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
- 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
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
def table Group.arel_table end
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
When breaking apart a query to use Arel, I find a good rule of thumb is to
break out a method anywhere the word
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.
- Learn more about composition over inheritance in Ruby with Ruby Science.