Arel SQL AST Manager for Ruby on Rails Cheat Sheet

Arel is a SQL AST (Abstract Syntax Tree) manager for Ruby that powers ActiveRecord's programmatic query generation in Ruby on Rails.

Tables & Attributes

# From an ActiveRecord model
users = User.arel_table

# Or as an independent table definition
users = Arel::Table.new(:users)

# Accessing columns/attributes
users[:name]
users[:id]

---

Projections (SELECT) & Predicates (WHERE)

Selection & Projections

users.project(users[:id], users[:name])
# SELECT users.id, users.name FROM users

Predicates

users.where(users[:name].eq('Amy'))
# SELECT * FROM users WHERE users.name = 'Amy'

users.where(users[:age].gt(18).and(users[:active].eq(true)))
# WHERE users.age > 18 AND users.active = 1

users.where(users[:role].in(['admin', 'editor']))
# WHERE users.role IN ('admin', 'editor')

users.where(users[:email].matches('%@example.com'))
# WHERE users.email LIKE '%@example.com'

---

Table Joins

Basic Inner & Outer Joins

photos = Photo.arel_table

# Inner Join
users.join(photos).on(users[:id].eq(photos[:user_id]))

# Left Outer Join
users.join(photos, Arel::Nodes::OuterJoin).on(users[:id].eq(photos[:user_id]))

Aliased Complex Joins

Joining the same table multiple times with distinct semantic aliases:

creators = User.arel_table.alias('creators')
updaters = User.arel_table.alias('updaters')
photos   = Photo.arel_table

query = photos
  .join(photos.join(creators, Arel::Nodes::OuterJoin).on(photos[:created_by_id].eq(creators[:id])))
  .join(photos.join(updaters, Arel::Nodes::OuterJoin).on(photos[:assigned_id].eq(updaters[:id])))
  .project(photos[:name], photos[:created_at], creators[:name].as('creator'), updaters[:name].as('editor'))

# Generate raw SQL string
query.to_sql

---

Pagination & Ordering

users.take(10) # LIMIT 10
users.skip(20) # OFFSET 20

users.order(users[:name].asc)
users.order(users[:name].asc, users[:created_at].desc)

---

Aggregations & Calculations

users.project(users[:id].count)                  # COUNT(users.id)
users.project(users[:id].count.as('total_users'))# COUNT(users.id) AS total_users
users.project(users[:age].average)               # AVG(users.age)
users.project(users[:age].sum)                   # SUM(users.age)
users.project(users[:age].maximum)               # MAX(users.age)

---

Integrating with ActiveRecord Scopes

class User < ApplicationRecord
  scope :with_custom_score, -> {
    users = arel_table
    where(users[:score].gteq(100))
  }
end