outer join
posts = Arel::Table.new(:posts)
comments = Arel::Table.new(:comments)
query = posts.
project(posts[:title]).
join(comments, Arel::Nodes::OuterJoin).
on(posts[:id].eq(comments[:post_id])).
where(comments[:starred].gt(50))
query.to_sql
# => SELECT "posts"."title"
# FROM "posts"
# LEFT OUTER JOIN "comments" ON "posts"."id" = "comments"."post_id"
# WHERE "comments"."starred" > 50
Slide 12
Slide 12 text
or
posts = Arel::Table.new(:posts)
query = posts.
project(Arel.star).
where(posts[:status].eq(:published).or(posts[:status].eq(:archived)))
query.to_sql
# => SELECT *
# FROM "posts"
# WHERE ("posts"."status" = 'published' OR "posts"."status" = 'archived')
Slide 13
Slide 13 text
math
posts = Arel::Table.new(:posts)
query = posts.
project((posts[:starred_comments_count] / posts[:comments_count]).as("ratio"))
query.to_sql
# => SELECT "posts"."starred_comments_count" / "posts"."comments_count" AS ratio
# FROM "posts"
Slide 14
Slide 14 text
literals
users = Arel::Table.new(:users)
users_case = Arel::Nodes::SqlLiteral.new(<<-SQL
CASE WHEN condition1 THEN calculation1
WHEN condition2 THEN calculation2
WHEN condition3 THEN calculation3
ELSE default_calculation END
SQL
)
users.project(users_case.as("users_case"))
# => SELECT CASE WHEN condition1 THEN calculation1
# WHEN condition2 THEN calculation2
# WHEN condition3 THEN calculation3
# ELSE default_calculation END
# FROM "photos"
Slide 15
Slide 15 text
named functions
posts = Arel::Table.new(:posts)
lower_title = Arel::Nodes::NamedFunction.new("lower", [posts[:title]])
query = posts.project(lower_title)
query.to_sql
# => SELECT lower("posts"."title") FROM "posts"
Slide 16
Slide 16 text
ransack
used for creation of simple and advanced search forms
works with pagination
integrated with SimpleForm
Slide 17
Slide 17 text
configuration
controller default parameter :q
def index
@q = Person.ransack(params[:q])
@people = @q.result
end
form helper
<%= search_form_for @q do |f| %>
<%= f.label :name_cont %>
<%= f.search_field :name_cont %>
<%= f.label :last_name_start %>
<%= f.search_field :last_name_start %>
<% end >
Slide 18
Slide 18 text
usage
Person.ransack(age_gteq: 18)
# => SELECT "people".* FROM "people" WHERE ("people"."age" >= 18)
Post.ransack(person_first_name_or_person_last_name_cont: "Name")
# => SELECT "posts".*
# FROM "posts"
# LEFT OUTER JOIN "people" ON "people"."id" = "posts"."person_id"
# WHERE ("people"."first_name" LIKE '%Name%' OR
# "people"."last_name" LIKE '%Name%')
Slide 19
Slide 19 text
usage
class Post < ActiveRecord::Base
scope :by_status, ->(status="published") { where(status: status)}
def self.ransackable_scopes(auth_object = nil)
%i(by_status)
end
end
Post.ransack({ by_status: 'archived', created_at_lt: 1.month.ago.beginning_of_month })
# => SELECT "posts".*
# FROM "posts"
# WHERE "posts"."status" = 'archived' AND
# ("posts"."created_at" < '2015-05-01 00:00:00.000000')
Summary
there are betters ways for building SQL by concatenating strings
when ActiveRecord is not enought Arel is handy (or, not, likes, etc.)
you can build forms quickly with Ransack
Slide 22
Slide 22 text
Bonus
Slide 23
Slide 23 text
Squeel
SELECT "people".* FROM people
WHERE ("people"."name" LIKE 'Ernie%' AND "people"."salary" < 5000)
OR ("people"."name" LIKE 'Joe%' AND "people"."salary" > 10000)
Person.where(
'(name LIKE ? AND salary < ?) OR (name LIKE ? AND salary > ?)',
'Ernie%', 5000, 'Joe%', 10000
)
people = Arel::Table.new(:people)
people.
project(Arel.star).
where(people[:name].matches('Ernie%').and(people[:salary].lt(5000)).
or(people[:name].matches('Joe%').and(people[:salary].gt(10000)))).to_sql
Person.where{(name =~ 'Ernie%') & (salary < 5000) |
(name =~ 'Joe%') & (salary > 10000)}