Arel through examples

When a database query cannot be described using Ruby on Rails’ short ActiveRecord syntax, consider building your queries using Arel instead of going for raw SQL strings.

This has the following advantages:

Escaping by default: this helps not end up with SQL injections. We don’t like those.
Full names: using "users"."email" instead of email in your queries helps you be future-proof by making queries that won’t break when you merge queries together or add columns with conflicting names.
Composable: Arel expressions allows you to mix SQL commands as Ruby objects.
Uniform queries: it ensures that the generated SQL always looks the same. For example "users"."email" ASC instead of email asc. This makes it easier to search through logs and can help caching mechanisms.

Shorthand for `arel_table`

When writing Arel in Rails you access columns through the arel_table method on your models like so: User.arel_table[:email]. However, there is a lovely little shortcut that is often added to shorten this:

class ApplicationRecord < ActiveRecord::Base
  def self.[](attribute)
    arel_table[attribute]
  end
end

This way you can write User[:email] instead of User.arel_table[:email] 🎉.

Here are some examples of using Arel, using the shorthand syntax.

Greater or lower than

Using lt, lteq, gt or gteq.

User.where(User[:created_at].lt(1.day.ago))
# => SELECT "users".* FROM "users" WHERE "users"."created_at" < '2021-01-01 00:00:00.000000'

Case insentive search

Using .lower.

email = "[email protected]"
User.where(User[:email].lower.eq(email.downcase))
# => SELECT "users".* FROM "users" WHERE LOWER("users"."email") = '[email protected]'

Search with partial matches

Using matches or matches_regexp.

User.where(User[:email].matches("%@example.org"))
# => SELECT "users".* FROM "users" WHERE "users"."email" ILIKE '%@example.org'

Comparing fields

User.where(User[:firstname].eq(User[:lastname]))
# => SELECT "users".* FROM "users" WHERE "users"."firstname" = "users"."lastname"

Concatenating strings

Using concat.

space = Arel.sql("' '")
User.pluck(User[:first_name].concat(space).concat(User[:last_name]))
# => SELECT "users"."first_name" || ' ' || "users"."last_name" FROM "users"

Composing conditions

Using and and or between conditions and using eq, not_eq, in, and not_in on columns.

is_admin = User[:admin].eq(true)
is_moderator = User[:moderator].eq(true).and(User[:activated_at].not_eq(nil))
User.where(is_admin.or(is_moderator))
# => SELECT "users".* FROM "users" WHERE ("users"."admin" = TRUE OR "users"."moderator" = TRUE AND "users"."activated_at" IS NOT NULL)

Math

Using +, -, /, *.

price = Order[:price_cents]
vat_rate = Order[:vat_rate]
Order.sum(price + price * vat_rate)
# => SELECT SUM(("orders"."price_cents" + "orders"."price_cents" * "orders"."vat_rate")) FROM "orders"

Aggregate functions

Such as sum, count, average, minimum or maximum.

User.select(:role, User[:id].minimum, User[:id].maximum).group(:role)
# =>  SELECT "users"."role", MAX("users"."id"), MIN("users"."id") FROM "users" GROUP BY "users"."role"

Group and filter by columns from other tables

Since the table_name: :column_name syntax isn’t accepted in ActiveRecord’s group and having.

User.joins(:orders).group(Order[:id]).having(Order[:created_at].lt(1.year.ago)).count
# => SELECT COUNT(*) AS count_all, "orders"."id" AS orders_id FROM "users" INNER JOIN "orders" ON "orders"."user_id" = "users"."id" GROUP BY "orders"."id" HAVING "orders"."created_at" < '2020-02-20 17:51:14.117027'

Pluck columns from other tables

Since the table_name: :column syntax isn’t accepted in ActiveRecord’s pluck and pick.

User.joins(:comments).pluck(Comment[:id])
# => SELECT "comments"."id" FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id"

Transform timestamps

Extracting parts of a date or time with extract.

User.distinct.pluck(User[:created_at].extract("year"))
# => SELECT DISTINCT EXTRACT(YEAR FROM "users"."created_at") FROM "users"

Order by other tables

ActiveRecord’s order does not accept the table: :column syntax but accepts an Arel column directly, optionally followed by asc or desc.

User.joins(:comments).order(Comment[:created_at].desc)
# => SELECT "users".* FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" ORDER BY "comments"."created_at" DESC

Order with null values

Using nulls_last or nulls_first.

User.order(User[:email].desc.nulls_last)
# => SELECT "users".* FROM "users" ORDER BY "users"."email" ASC NULLS LAST

Order by a custom order

Using Arel::Nodes::Case.

User.order(
  Arel::Nodes::Case
    .new(User[:role])
    .when("admin").then(1)
    .when("editor").then(2)
)
# => SELECT "users".* FROM "users" ORDER BY CASE "users"."role" WHEN 'admin' THEN 1 WHEN 'editor' END

Query values including NULLs

Because .where.not can be counter-intuitive regarding NULL values, you can use is_distinct_from to include them.

User.where(User[:active].is_distinct_from(true))
# => SELECT "users".* FROM "users" WHERE "users"."active" IS DISTINCT FROM TRUE

Search in array columns

To filter through a column that uses PostgreSQL’s array type.

any_role = Arel::Nodes::NamedFunction.new("ANY", [User[:roles]])
User.where(Arel::Nodes.build_quoted("admin").eq(any_role))
# => SELECT "users".* FROM "users" wHERE 'admin' = ANY("users"."roles")

Return the rounded division of columns

In this example we are doing math between columns, wrapping the result with the ROUND function and exposing it using as. Arel.star lets us select all other fields from the table as well.

score = User[:followers_count] / User[:followees_count]
rounded_score = Arel::Nodes::NamedFunction.new("ROUND", [commission])

User
  .select(User[Arel.star])
  .select(rounded_score.as("score"))
# => SELECT "users".*, ROUND("users"."followers_count" / "users"."followees_count") AS score FROM "users"

Custom SQL

Use Arel.sql and to_sql if you need to compose raw SQL strings with Arel nodes and ActiveRecord scopes.

articles_sql = Article.hidden.select(:id).to_sql
hidden_article_ids = Comment[:article_id].in(Arel.sql(articles_sql))
is_spam = Comment[:is_spam].eq(true)

Comment.where(hidden_article_ids.or(is_spam))
# => SELECT "comments".* FROM "comments" WHERE ("comments"."article_id" IN (SELECT "articles"."id" FROM "articles" WHERE "articles"."hidden" = TRUE)) OR "comments"."is_spam" = TRUE

A final word of warning

A caveat to keep in mind is that Arel is considered a private API and these calls could break in future Rails versions (7+). However, major breaking changes are unlikely since Arel was a public gem before it was integrated to Rails 5 and a lot of developers are using them to simplify their queries.

As always, whether you are building custom SQL or using Arel, make sure your code is covered by tests.

Happy Areling! ✨

Sunfox