Arel cheatsheet on Steroids

arel_cheatsheet_on_steroids.md

Arel Cheatsheet on Steroids

A (more) complete cheatsheet for Arel, including NamedFunction functions, raw SQL and window functions.

Tables

posts = Arel::Table.new(:posts)
posts = Post.arel_table # ActiveRecord

Table alias

posts = Post.arel_table.alias(:user_posts) # `posts` `user_posts`

Columns

Base

posts[:id] # `posts`.`id`
posts[:title] # `posts`.`title`
posts[Arel.star] # `posts`.*
posts[:title].as('name') # `posts`.`title` AS name

Grouping functions

posts[:id].count # COUNT(`posts`.`id`)
posts[:answers_count].sum # SUM(`posts`.`id`)
posts[:id].minimum # MIN(`posts`.`id`)
posts[:id].maximum # MAX(`posts`.`id`)
posts[:rating].average # AVG(`posts`.`rating`)

Math operations

(posts[:answers_count] + posts[:likes_count]).as('engagement') # (`posts`.`answers_count` + `posts`.`likes_count`) AS engagement
posts[:answers_count] * 2 # (`posts`.`answers_count` * 2)
# If you want to sum more than 2 columns, it's a little bit trickier
(posts[:col_a] + posts[:col_a]).expr + posts[:col_c] # `posts`.`col_a` + `posts`.`col_b` + `posts`.`col_c`

Mapping custom SQL functions with Arel

users = User.arel_table

Arel::Nodes::NamedFunction.new(
  'IF',
  [users[:name].eq(nil), users[:email], users[:name]]
).as('name_or_email') # IF(`users`.`name` IS NULL, `users`.`email`, `users`.`name`) AS name_or_email

 # Wrapping value for NamedFunctions
Arel::Nodes.build_quoted('some value')
 # Example
Arel::Nodes::NamedFunction.new('DATE_FORMAT', [posts[:created_at], Arel::Nodes.build_quoted('%Y%m')]) # DATE_FORMAT(`posts`.`created_at`, '%Y%m')

Select

posts.project(:id, :title) #=> "SELECT id, title FROM posts"
posts.project(posts[:id], posts[:title]) #=> "SELECT `posts`.`id`, `posts`.`title` FROM `posts`"
posts.project(posts[Arel.star]) #=> "SELECT `posts`.`*` FROM `posts`"

Conditions

posts[:id].eq(3) # `posts`.`id` = 3
posts[:name].eq(nil) # `posts`.`name` IS NULL
posts[:answers_count].gt(3) # `posts`.`answers_count` > 3
posts[:answers_count].lteq(3) # `posts`.`answers_count` <= 3
posts[:id].in(2, 3, 4) # `posts`.`id` IN (2, 3, 4)
posts[:title].matches('hell%') # `posts`.`title` LIKE 'hell%'

AND/OR

posts[:id].eq(3).and(posts[:name].eq('hello')) # `posts`.`id` = 3 AND `posts`.`name` = 'hello'
users[:id].eq(2).and(users[:last_name].eq('doe').or(users[:first_name].eq('john'))) # `users`.`id` = 2 AND (`users`.`last_name` = "doe" OR `users`.`first_name` = 'john')

Wrap raw SQL in arel:

Arel.sql('`posts`.`id` = 3') # Wrapping condition

Joins

comments = Comment.arel_table
posts.join(comments) # INNER JOIN `comments`
posts.join(comments).on(posts[:id].eq(comments[:post_id])) # INNER JOIN `comments` ON `posts`.`id` = `comments`.`post_id`

LEFT OUTER JOINS

posts.join(comments.alias(:post_comments), Arel::Nodes::OuterJoin).on(posts[:id].eq(comments[:post_id])) # LEFT OUTER JOIN `comments` `post_comments` ON `posts`.`id` = `post_comments`.`post_id`

JOINS WITH SUBQUERIES

sub_join = posts.join(comments).on(posts[:id].eq(comments[:post_id])).project(:id, :user_id).as('commented_posts')
users.join(
  sub_join
).on(sub_join[:user_id].eq(users[:id])) # SELECT * FROM `users` INNER JOIN (SELECT * FROM `posts` INNER JOIN `comments` ON `users`.`id` = `comments`.`post_id`) commented_posts ON `commented_posts`.`user_id` = `users`.`id`

More examples: https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb#L533

GROUP BY

users.group(users[:id], users[:name]) # GROUP BY `users`.`id`, `users`.`name`

Limit / Skip

users.take(10) # SELECT * FROM `users` LIMIT 10
users.skip(5) # SELECT * FROM `users` OFFSET 5

Order

users[:id].desc # `users`.`id` DESC
User.order(users[:id].desc).to_sql # SELECT FROM `users` ORDER BY `users`.`id` DESC

Window functions

window = Arel::Nodes::Window.new.order(users[:name])
users[:id].count.over(window) # COUNT(`users`.`id`) OVER (ORDER BY `users`.`name`)

# Partition
window = Arel::Nodes::Window.new.partition(users[:name])
users[:id].count.over(window) # COUNT(`users`.`id`) OVER (PARTITION BY `users`.`name`)

More examples here: https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb#L728

WITH and WITH RECURSIVE

users_top_name  = Arel::Table.new(:users_top) # Define name `users_top`
top_users_query = users.project(users[:id]).where(users[:karma].gt(100)) # Define the query to put into the WITH() statement
users_as        = Arel::Nodes::As.new(users_top_name, top_users_query) # Alias the query as `users_top`

comments.project(Arel.star)
        .with(users_as) # or with(:recursive, users_as)
        .where(comments[:author_id].in(users_top.project(users_top[:id])))
# =>  WITH "users_top" AS (SELECT "users"."id" FROM "users" WHERE "users"."karma" > 100)
#     SELECT * FROM "comments" WHERE "comments"."author_id" IN (
#       SELECT "users_top"."id" FROM "users_top"
#     )

Extending Arel

class ArelExt::SumIf < Arel::Nodes::NamedFunction
  def initialize(condition, column)
    super('SUM', [Arel::Nodes::NamedFunction.new('IF', [condition, column, 0])])
  end
end

module ArelSumIfConcern
  include ActiveSupport::Concern

  def sum_if(condition)
    ArelExt::SumIf.new(condition, self)
  end
end

Arel::Attributes::Attribute.include ArelSumIfConcern

Usage:

User.arel_table[:comments_count].sum_if(User.arel_table[:first_name].matches("gian%")).to_sql # SUM(IF(`users`.`first_name` LIKE 'gian%', `users`.`comments_count`, 0))

Loooots of examples directly in the tests:

https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb