AREL tricks
sources:
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 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
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: