Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Building SQL queries with AREL and Ransack

45617fd77f12f016894c0931771b2a98?s=47 mbie
June 30, 2015

Building SQL queries with AREL and Ransack

45617fd77f12f016894c0931771b2a98?s=128

mbie

June 30, 2015
Tweet

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