ActiveRecord can't do it, Arel can!

1b0973b64704738dbc8ce24d8382bb1f?s=47 Prathamesh
January 10, 2015
3.2k

ActiveRecord can't do it, Arel can!

Talk given at GCRC 2015 with @vipulnsward

1b0973b64704738dbc8ce24d8382bb1f?s=128

Prathamesh

January 10, 2015
Tweet

Transcript

  1. ActiveRecord can’t do it Arel can!

  2. Prathamesh Sonpatki @_cha1tanya Vipul A M @vipulnsward

  3. BigBinary.com/jobs videos.BigBinary.com

  4. Welcome, to second conference.name

  5. #gardenCityRubyConferenceIsTheBe stThingToHappenToThePlanet A conference with a (59) character(s)

  6. None
  7. Pune.rb http://deccanrubyconf.org

  8. RubyIndia.org

  9. Podcast.RubyIndia.org

  10. This is not magenta

  11. None
  12. Backpackers • Task management • Good locations • Food centres

    • Cheap hostels/hotels
  13. Finding lost bags @rkh_bag

  14. Happy Customers

  15. None
  16. class Traveler < ActiveRecord::Base has_many :reviews has_many :bookings end class

    Task < ActiveRecord::Base belongs_to :location end
  17. class Location < ActiveRecord::Base has_many :nearby_locations, class_name: 'Location', foreign_key: 'parent_location_id'

    has_many :reviews has_many :bookings end class Booking < ActiveRecord::Base belongs_to :location belongs_to :traveler end
  18. backpackerz.herokuapp.com

  19. 30+ active Users ➜ ~ heroku run rails console --app

    backpackerz Running rails console attached to terminal... up, run.6589 Loading production environment (Rails 4.1.1) irb(main):001:0> User.pluck(:email).uniq.count => 31
  20. Performanze is most importantz.

  21. Raw SQL ftw!

  22. None
  23. Searching for Indian food! class Restaurant < ActiveRecord::Base def self.food_search_raw

    search_term where("cuisine ILIKE ? OR cuisine ILIKE ?", '%indian%', "%#{search_term}%" ) end end
  24. Searching for Indian food! Restaurant.food_search_raw(“india”) #=> # SELECT restaurants.* FROM

    restaurants # WHERE (cuisine ILIKE '%indian%' OR cuisine ILIKE '%india%')
  25. Arel

  26. Arel.is_a? • SQL Generator for Ruby • Based on Relational

    Algebra • Used by ActiveRecord under the hood(Rails 3+) • Lots of features not exposed through ActiveRecord API
  27. Arel.is_a? • SQL Generator for Ruby • Based on Relational

    Algebra • Used by ActiveRecord under the hood(Rails 3+) • Lots of features not exposed through ActiveRecord API • Largely Undocumented
  28. Arel != ActiveRecord • Job of Arel is to just

    generate SQL. • Arel does not know about database. • Arel does not execute the generated SQL.
  29. Arel != ActiveRecord Arel ActiveRecord Database

  30. Arel != ActiveRecord Arel ActiveRecord Database

  31. How to use Arel? Gem::Specification.new do |s| s.platform = Gem::Platform::RUBY

    s.name = 'activerecord' .... s.add_dependency 'activesupport', version s.add_dependency 'activemodel', version s.add_dependency 'arel', '7.0.0.alpha' end
  32. Arel table def arel_table @arel_table ||= Arel::Table.new(table_name) end Contact.arel_table =>

    #<Arel::Table:0x007fad4bd15fb0 @name="contacts", @engine=Contact(id: integer, user_id: integer, contact_type: string, data: string, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>
  33. Arel table class Restaurant < ActiveRecord::Base ... private def self.table

    arel_table end end Restaurant.table
  34. Searching for Indian food! class Restaurant < ActiveRecord::Base def self.food_search

    search_term where(table[:cuisine].matches_any(['%indian%', "%#{search_term}%"])) end end # SELECT restaurants.* FROM restaurants # WHERE ((restaurants.cuisine ILIKE '%indian%' OR # restaurants.cuisine ILIKE '%india%'))
  35. Arel::Attribute table[:cuisine].matches_any(['%indian%', "%#{search_term}%"])

  36. Arel::Attribute table[:cuisine].matches_any(['%indian%', "%#{search_term}%"]) Similarly, table[:pricing] table[:location]

  37. Predicates table[:cuisine].matches_any(['%indian%', "%#{search_term}%"])

  38. Searching for Indian food! table[:cuisine].matches_any(['%indian%',"%#{search_term}%"]) AST

  39. Searching for Indian food class Restaurant < ActiveRecord::Base def self.food_search

    search_term where (AST) end end
  40. Searching for Indian food class Restaurant < ActiveRecord::Base def self.food_search

    search_term where(table[:cuisine].matches_any(['%indian%',"%#{search_term}%"])) end end # SELECT restaurants.* FROM restaurants # WHERE ((restaurants.cuisine ILIKE '%indian%' OR restaurants.cuisine # ILIKE '%india%')) where(a: something) where(“a like ?”, something) where(AST)
  41. Searching for Indian food class Restaurant < ActiveRecord::Base def self.food_search

    search_term where(table[:cuisine].matches_any(['%indian%',"%#{search_term}%"])) end end # SELECT restaurants.* FROM restaurants # WHERE ((restaurants.cuisine LIKE '%indian%' OR restaurants.cuisine # LIKE '%india%')) Same query on MySQL database
  42. Database agnostic

  43. Database agnostic • MySQL • PostgreSQL • SQLite • Oracle

    • SQL server
  44. No Non-Vegetarian

  45. None
  46. I don’t want non-vegetarian food! class Restaurant < ActiveRecord::Base def

    self.food_search_not search_term where("cuisine_type NOT ILIKE '%nonveg%'") end end # SELECT restaurants.* FROM restaurants WHERE # (restaurants.cuisine_type NOT ILIKE '%nonveg%')
  47. I don’t want non-vegetarian food class Restaurant < ActiveRecord::Base def

    self.food_search_not search_term where(table[:cuisine_type].does_not_match('%nonveg%')) end end # SELECT restaurants.* FROM restaurants WHERE # (restaurants.cuisine_type NOT ILIKE '%nonveg%')
  48. List of all predicates • eq • eq_any • eq_all

    • in • in_any • in_all • matches • matches_all • matches_any • not_eq • not_eq_any • not_eq_all • gt/lt • gteq/lteq • gteq_any/lteq_any • gteq_all/lteq_all • gt_any/lt_any • gt_all/lt_all • not_in • not_in_any • not_in_all • does_not_match • does_not_match_any • does_not_match_all
  49. None
  50. Wait. But.

  51. Locations with bookings && rating > 3 [ Location.joins("INNER JOIN

    reviews ON locations.id = reviews.location_id AND reviews.rating > ?", rating), Location.joins(:bookings) ].map(&:to_sql).join(" INTERSECT ")
  52. Locations with bookings && rating > 3 # SELECT *

    FROM locations # INNER JOIN reviews ON locations.id = reviews.location_id # AND reviews.rating > 3 INTERSECT # SELECT locations.* FROM locations INNER JOIN bookings ON # bookings.location_id = locations.id
  53. Reviews with rating > 3 # All reviews having rating

    greater than 3 class Review def self.having_rating_more_than rating table[:rating].gt(rating) end end #=> "\"reviews\".\"rating\" > 3"
  54. Locations with bookings # All locations with bookings Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]))

    #=> "INNER JOIN \"reviews\" ON \"locations\".\"id\" = \" reviews\".\"location_id\""
  55. Join Arel way Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]))

  56. Join Arel way Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]))

  57. Join Arel way Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id])) Predicate

  58. Join Arel way Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id])) Location.table[:id].gt(something)

  59. Join Arel way Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3))) #=> INNER JOIN \"reviews\"

    ON \"locations\".\"id\" = \" reviews\".\"location_id\" AND \"reviews\".\"rating\" > 3"
  60. Locations with bookings && rating > 3 Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3)))

    .project(Arel.star) #=> “*” #=> "SELECT * FROM \"locations\" INNER JOIN \"reviews\" ON \" locations\".\"id\" = \"reviews\".\"location_id\" AND \" reviews\".\"rating\" > 3"
  61. Outer join Location.table.join(Review.table, Arel::OuterJoin) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3))) .project(Arel.star)

  62. Right outer join Location.table.join(Review.table, Arel::RightOuterJoin) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3))) .project(Arel.star)

  63. Full outer join Location.table.join(Review.table, Arel::FullOuterJoin) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3))) .project(Arel.star)

  64. None
  65. Focus.

  66. Locations with bookings && rating > 3 # All locations

    with reviews having rating more than 3 Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3))) .project(Arel.star) # All locations with bookings Location.joins(:bookings)
  67. Locations with bookings && rating > 3 # Combine them

    using intersect Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3))) .project(Arel.star) .intersect(Location.joins(:bookings))
  68. Locations with bookings && rating > 3 # SELECT *

    FROM locations INNER JOIN reviews # ON locations.id = reviews.location_id # AND reviews.rating > 3 # INTERSECT # SELECT locations.* FROM locations INNER JOIN bookings # ON bookings.location_id = locations.id
  69. Unions Location.table.join(Review.table) .on(Location.table[:id].eq(Review.table[:location_id]) .and(Review.having_rating_more_than(3))) .project(Arel.star) .union(Location.joins(:bookings))

  70. Unions # SELECT * FROM locations INNER JOIN reviews #

    ON locations.id = reviews.location_id # AND reviews.rating > 3 # UNION # SELECT locations.* FROM locations INNER JOIN bookings # ON bookings.location_id = locations.id
  71. All kinds of combinationz possible!

  72. Okhay. Then.

  73. All hotels within 5 km of malls class Location <

    ActiveRecord::Base has_many :nearby_locations, class_name: 'Location', foreign_key: 'parent_location_id' ... end
  74. All hotels within 5 km of malls def self.all_nearby_locations_within_distance_from_parent(distance =

    5) joins("INNER JOIN locations nearby_locations ON locations.id = nearby_locations.parent_location_id AND nearby_locations.distance_from_parent <= #{distance}" ) end # SELECT "locations".* FROM "locations" INNER JOIN locations # nearby_locations ON locations.id = nearby_locations.parent_location_id # AND nearby_locations.distance_from_parent <= 5
  75. All hotels within 5 km of malls class Location <

    ActiveRecord::Base has_many :nearby_locations, class_name: 'Location', foreign_key: 'parent_location_id' ... end
  76. All hotels within 5 km of malls class Location <

    ActiveRecord::Base … nearby_locations = table.alias(“nearby_locations”) table.join(nearby_locations) .on(table[:id].eq(nearby_locations[:parent_location_id]) .join_sources #=> [Arel::Nodes::Join] end
  77. All hotels within 5 km of malls class Location <

    ActiveRecord::Base ... table.join(nearby_locations) .on(table[:id].eq(nearby_locations[:parent_location_id]) .and(nearby_locations[:distance_from_parent].lteq(5)) .join_sources end
  78. All hotels within 5 km of malls Location.joins(table.join(nearby_locations) .on(table[:id].eq(nearby_locations[:parent_location_id]) .and(nearby_locations[:distance_from_parent].lteq(5))

    .join_sources ) [Arel::Node::Join]
  79. All hotels within 5 km of malls nearby_locations = table.alias

    Location.joins(table.join(nearby_locations) .on(table[:id].eq(nearby_locations[:parent_location_id]) .and(nearby_locations[:distance_from_parent].lteq(5)) .join_sources ) #=> SELECT "locations".* FROM "locations" INNER JOIN "locations" "nearby_locations" ON "locations"."id" = "nearby_locations"."parent_location_id" AND "nearby_locations"."distance_from_parent" <= 5
  80. None
  81. Super Securez def set_sekret(value = 'lol', key = 'sekret') escape_and_execute(["SELECT

    pgp_sym_encrypt(?, ?)", value.to_s, key])['pgp_sym_encrypt'] end def get_sekret(value = 'lol', key = 'somesekret') escape_and_execute(["SELECT pgp_sym_decrypt(?, ?)", value, key])['pgp_sym_decrypt'] rescue raise 'lol; get it right bro' end def escape_and_execute(query) query = ::ActiveRecord::Base.send :sanitize_sql_array, query ::ActiveRecord::Base.connection.execute(query).first end
  82. Named functions • Wrappers for SQL functions • Makes it

    easy to reuse even SQL functions
  83. Named functions def pgp_sym_encrypt(value = 'lol', key = 'sekret') Arel::Nodes::NamedFunction.new('pgp_sym_encrypt',

    [value, key]) end #=> pgp_sym_encrypt('lol', 'sekret') def arel_sekret(value = 'lol', key = 'sekret') User.select(pgp_sym_encrypt(value, key)) end #=> SELECT pgp_sym_encrypt('lol', 'sekret') FROM \"users\"
  84. Named functions def pgp_sym_encrypt(value = 'lol', key = 'sekret') Arel::Nodes::NamedFunction.new('pgp_sym_encrypt',

    [value, key], ‘pgp_encrypted’) end #=> pgp_sym_encrypt('lol', 'sekret') AS pgp_encrypted" def arel_sekret(value = 'lol', key = 'sekret') User.select(pgp_sym_encrypt(value, key)) end #=> SELECT pgp_sym_encrypt('lol', 'sekret') AS pgp_encrypted FROM \"users\"
  85. Other example of Named functions class Task < ActiveRecord::Base def

    self.order_criteria_raw "coalesce('completed_at', 'created_at')" end end Task.order(Task.order_criteria_raw) #=> SELECT "tasks".* FROM "tasks" ORDER BY coalesce('completed_at', 'created_at')
  86. Other example of Named functions class Task < ActiveRecord::Base def

    self.order_criteria Arel::Nodes::NamedFunction.new('coalesce', ['completed_at', 'created_at']) end end Task.order(Task.order_criteria_raw) #=> SELECT "tasks".* FROM "tasks" ORDER BY coalesce('completed_at', 'created_at')
  87. Summmery • Predicatez • Combinationz • Joinz • Arel +

    ActiveRecord!
  88. None
  89. This is not magenta, either.

  90. Thank You! @_cha1tanya @vipulnsward @BigBinary BigBinary.com/jobs code: github.com/prathamesh-sonpatki/activerecord-with-arel app: backpackerz.herokuapp.com