Share
Embed
Transcript
26 slides
-
ruby talks #5 building SQL queries with Arel and Ransack
created by Mateusz Bielec -
what? A Relational Algebra SQL Abstract Syntax Tree (AST) manager
for Ruby used in ActiveRecord it creates only SQL queries -
simple query posts = Arel::Table.new(:posts) # Post.arel_table for ActiveRecord class
query = posts.project(Arel.star) query.to_sql # => SELECT * FROM "posts" -
conditions posts = Arel::Table.new(:posts) query = posts. project(Arel.star) where(posts[:status].eq(:published)). where(posts[:comments_count].gteq(5)).
where(posts[:title].matches("Ruby")) query.to_sql # => SELECT * FROM "posts" # WHERE "posts"."status" = 'published' AND # "posts"."comments_count" >= 5 AND # "posts"."title" ILIKE 'Ruby' -
conditions eq - (a = 1) in - (a in
[1, 2]) matches - (a like "%one%") lt - (a < 1) gt - (a > 1) gteq - (a >= 1) lteq - (a <= 1) -
join posts = Arel::Table.new(:posts) comments = Arel::Table.new(:comments) query = posts.
project(Arel.star) join(comments).on(posts[:id].eq(comments[:post_id])). where(comments[:starred].gt(50)) query.to_sql # => SELECT * FROM "posts" # INNER JOIN "comments" ON "posts"."id" = "comments"."post_id" # WHERE "comments"."starred" > 50 -
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 -
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') -
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" -
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" -
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" -
ransack used for creation of simple and advanced search forms
works with pagination integrated with SimpleForm -
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 > -
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%') -
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') -
Sorting <%= sort_link(@q, :name, "Full name") %> <%= sort_link(@q, :age,
default_order: :desc) %> <%= sort_link(@q, 'posts.created_at', hide_indicator: true) %> -
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 -
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)}