A (more) complete cheatsheet for Arel, including NamedFunction functions, raw SQL and window functions.
posts = Arel::Table.new(:posts)
posts = Post.arel_table # ActiveRecord
posts = Post.arel_table.alias(:user_posts) # `posts` `user_posts`
posts[:id] # `posts`.`id`
posts[:title] # `posts`.`title`
posts[Arel.star] # `posts`.*
posts[:title].as('name') # `posts`.`title` AS name
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`)
(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`
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')
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`"
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%'
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')
Arel.sql('`posts`.`id` = 3') # Wrapping condition
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`
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`
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
users.group(users[:id], users[:name]) # GROUP BY `users`.`id`, `users`.`name`
users.take(10) # SELECT * FROM `users` LIMIT 10
users.skip(5) # SELECT * FROM `users` OFFSET 5
users[:id].desc # `users`.`id` DESC
User.order(users[:id].desc).to_sql # SELECT FROM `users` ORDER BY `users`.`id` DESC
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
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"
# )
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))
https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb