Arel est un gestionnaire d'AST (arbre de syntaxe abstraite) SQL pour Ruby qui alimente la génération programmatique des requêtes ActiveRecord.
Tables et attributs (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) et prédicats (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'
Jointures de tables (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
Pagination et tri (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)
Agrégations et calculs
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)
Intégration avec les scopes ActiveRecord
class User < ApplicationRecord
scope :with_custom_score, -> {
users = arel_table
where(users[:score].gteq(100))
}
end