Hoja de trucos de Arel SQL AST Manager para Ruby on Rails

Arel es un gestor de AST (Árbol de Sintaxis Abstracta) SQL para Ruby que impulsa la generación programática de consultas de ActiveRecord.

Tablas y atributos (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]

---

Proyecciones (SELECT) y predicados (WHERE)

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

# Predicates (WHERE)
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'

---

Combinaciones de tablas (Table 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
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

---

Paginación y ordenación (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)

---

Agregaciones y cálculos

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)

---

Integración con ámbitos de ActiveRecord

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

---

Hojas de trucos relacionadas

• ActiveAdmin
• Devise