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