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

Building SQL queries with AREL and Ransack

mbie
June 30, 2015

Building SQL queries with AREL and Ransack

mbie

June 30, 2015
Tweet

More Decks by mbie

Other Decks in Programming

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