Building SQL queries with AREL and Ransack

Transcript

1. ruby talks #5
   building SQL queries with Arel and
   Ransack
   created by Mateusz Bielec
   

   View Slide

2. agenda
   arel
   ransack
   

   View Slide

3. what?
   A Relational Algebra
   SQL Abstract Syntax Tree (AST) manager for Ruby
   used in ActiveRecord
   it creates only SQL queries
   

   View Slide

4. why?
   abstraction/reuse
   readability
   reliability
   flexibility
   

   View Slide

5. when?
   complex SQL queries
   not
   or
   

   View Slide

6. arel in practice
   

   View Slide

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"
   

   View Slide

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'
   

   View Slide

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)
   

   View Slide

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
    

    View Slide

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
    

    View Slide

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')
    

    View Slide

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"
    

    View Slide

14. literals
    users = Arel::Table.new(:users)
    users_case = Arel::Nodes::SqlLiteral.new(<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"
    

    View Slide

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"
    

    View Slide

16. ransack
    used for creation of simple and advanced search forms
    works with pagination
    integrated with SimpleForm
    

    View Slide

17. configuration
    controller default parameter :q
    def index
    @q = Person.ransack(params[:q])
    @people = @q.result
    end
    form helper
    
    
    
    
    
    
    

    View Slide

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%')
    

    View Slide

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')
    

    View Slide

20. Sorting
    
    
    
    

    View Slide

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
    

    View Slide

22. Bonus
    

    View Slide

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)}
    

    View Slide

24. WAAT?
    

    View Slide

25. Questions?
    

    View Slide

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
    

    View Slide