Building SQL queries with AREL and Ransack

Transcript

1. ruby talks #5 building SQL queries with Arel and Ransack created by Mateusz Bielec
2. agenda arel ransack
3. what? A Relational Algebra SQL Abstract Syntax Tree (AST) manager for Ruby used in ActiveRecord it creates only SQL queries
4. why? abstraction/reuse readability reliability flexibility
5. when? complex SQL queries not or
6. arel in practice
7. simple query posts = Arel::Table.new(:posts) # Post.arel_table for ActiveRecord class query = posts.project(Arel.star) query.to_sql # => SELECT * FROM "posts"
8. 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'
9. 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)
10. 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
11. 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
12. 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')
13. 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"
14. 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"
15. 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"
16. ransack used for creation of simple and advanced search forms works with pagination integrated with SimpleForm
17. 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 >
18. 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%')
19. 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')
20. Sorting <%= sort_link(@q, :name, "Full name") %> <%= sort_link(@q, :age, default_order: :desc) %> <%= sort_link(@q, 'posts.created_at', hide_indicator: true) %>
21. 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
22. Bonus
23. 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)}
24. WAAT?
25. Questions?
26. Links https://github.com/rails/arel https://robots.thoughtbot.com/using-arel-to-compose-sql-queries http://danshultz.github.io/talks/mastering_activerecord_arel https://github.com/activerecord-hackery/ransack https://github.com/activerecord-hackery/squeel