6 Useful Arel Techniques for Composing Queries

By: Kyler Stole

If you ever struggle to express a query in ActiveRecord, you may be able to build it with Arel instead. Arel is the platform that manages the abstract syntax tree (AST) used to build SQL queries in Rails. It’s the implementation underneath the veneer of ActiveRecord (AR) and it’s considerably more flexible than just the functionality that AR exposes. When AR falls short of your needs, Arel can fill the gap without resorting to writing raw SQL.

What’s wrong with raw SQL?

Just a quick aside here. Nothing’s wrong with writing raw SQL. Sometimes that’s the best option when you have a hardcoded query block. However, avoiding it can be advantageous for a few reasons. When you’re building a query with a lot of variables and conditional logic, the raw SQL approach can quickly become less readable. As the complexity of your query increases, managing your growing SQL string while keeping valid syntax will become less tenable. That’s where taking the time to break your query into composable bits of Arel can be well worth the investment.

Additionally, Arel helps avoid SQL injection vulnerabilities and is more portable because the Arel AST still goes through your database’s adapter to build its query.

Note: Arel is technically a private API. It is likely to introduce breaking changes more often than public APIs, so if you use it directly, be sure to protect your code with sufficient unit test coverage. However, the core has remained fairly stable.

The starting point: #arel_table

The most common root of Arel usage is the #arel_table method. It’s a method defined on every model that inherits from ActiveRecord::Base, and it gives you an Arel::Table instance. With that starting point, you can construct any simple statement in a way that mirrors SQL.

User.arel_table[:name].eq('Timothy')
# users.name = 'Timothy'

User.arel_table[:age].gteq(27)
# users.age >= 27

User.arel_table[:status].in(%w[active pending])
# users.status IN ('active', 'pending')

Those all return an instance of a subclass of Arel::Nodes::Node and can be passed directly as the argument in an ActiveRecord #where.

All of the preceding examples have simple counterparts in AR using hash syntax, but Arel has support for a whole lot more.

User.arel_table[:name].not_in_any(%w[Mike Molly])
# users.name NOT IN ('Mike') OR users.name NOT IN ('Molly')

User.arel_table[:name].does_not_match_regexp('vic$')
# users.name !~ 'vic$'

User.arel_table[:name].is_distinct_from('Bob')
# users.name IS DISTINCT FROM 'Bob'

And importantly, these can take another Arel Attribute as an argument.

User.arel_table[:name].eq(Bot.arel_table[:name])
# users.name = bots.name

Discovering available methods

The previous section demonstrated a few predication methods, but there are a ton more. Since Arel is a private API, there is limited documentation, and discovering all available methods can require inspecting the source or perusing included modules.

Arel::Predications.instance_methods

The Arel::Predications module defines a long list of methods that can be used to build a WHERE condition, but there are others.

The Arel::Math module provides the mathematical operators you typically use in SQL so you can build up mathematical expressions.

column = User.arel_table[:age]
column /= age_divisor if age_divisor
column - Employee.arel_table[:time_at_company]
# users.age / 3 - employees.time_at_company

The Arel::Expressions module provides a few common functions you may use in aggregate queries.

User.arel_table[:age].maximum
# MAX(users.age)

The Arel::OrderPredications module adds #asc and #desc for ORDER BY clauses.

By any other name: #alias, #as

One of my gripes with ActiveRecord is that you can’t easily alias a table. If you join a table on itself via an association, it will create an alias in a deterministic way, but that’s entirely inflexible. The one exception is if you use the ActiveRecord #from method to supply a subquery, you can specify an alias as the second argument that overrides the default 'subquery' alias.

With Arel, any table can be aliased with #alias.

User.arel_table.alias('martians')

If you end up with a more complex Arel type that does not include the #alias method, such as Arel::SelectManager, you can achieve the same result by wrapping it manually.

Arel::Nodes::TableAlias.new(my_arel_obj, :martians)

Similarly, attributes can be aliased with #as.

User.arel_table[:name].as('first_name')

When Arel doesn’t have the method: NamedFunction

You can make any function that isn’t a method in Arel using the NamedFunction node type.

columns = [User.arel_table[:name], Bot.arel_table[:name]]
Arel::Nodes::NamedFunction.new('COALESCE', columns)
# COALESCE(users.name, bots.name)

column = User.arel_table[:age]
Arel::Nodes::NamedFunction.new('CAST', [column.as('float')])
# CAST(users.name AS float)

Unsurprisingly, this is the underlying implementation for a lot of built-in Arel methods.

One table is not enough: join sources

Creating legible joins is a shining point for ActiveRecord because you set up the associations and don’t need to specify the conditions. Arel doesn’t have that layer, so you have to be more explicit.

users_table = User.arel_table
join_table = User.arel_table.alias('employees')

conditions = [
  join_table[:id].eq(users_table[:id]),
  join_table[:dismissed].eq(false)
].reduce(:and)

query.joins(
  users_table.outer_join(join_table).on(conditions).join_sources
)
# FROM users LEFT OUTER JOIN users employees
#   ON employees.id = users.id AND employees.dismissed = FALSE

Just as with passing Arel nodes to an AR #where, the Arel join sources can be passed directly to an AR #joins.

Fake it till you make it: Arel.sql

When you do need to dip into the realm of raw SQL, you can still do it under an Arel umbrella! Apart from being the least exciting of the Arel methods demonstrated here, this may be the most useful. And since ActiveRecord began raising an error in Rails 6 for unrecognized attributes in the #order and #pluck clauses, this is likely a method you’ve used already. It effectively marks a raw string as known safe.

query.order(:age, Arel.sql('ARRAY_AGG(DISTINCT users.name)'))

Where to go from here

Arel can be very useful, but it’s not applicable in all cases. ActiveRecord should still be used where possible. Take a look at how we used a mix of Arel and ActiveRecord to create a composable query builder that powers several complex, dynamic pages.

We are hiring! If you love solving problems, please apply here. We would love to have you join us!