Arel SQL AST Manager Spickzettel für Ruby on Rails

Arel ist ein SQL-AST-Manager (Abstract Syntax Tree) für Ruby, der die programmatische Abfragegenerierung von ActiveRecord in Rails antreibt.

Tabellen und Attribute (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]

---

Projektionen (SELECT) und Prädikate (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'

---

Tabellenverknüpfungen (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

---

Paginierung und Sortierung (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)

---

Aggregationen und Berechnungen

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)

---

Integration mit ActiveRecord Scopes

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

---

Verwandte Spickzettel

• ActiveAdmin
• Devise