ActiveRecord can't do it, Arel can!

Talk given at GCRC 2015 with @vipulnsward

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)
7. Pune.rb http://deccanrubyconf.org
8. RubyIndia.org
9. Podcast.RubyIndia.org
10. This is not magenta
12. Backpackers • Task management • Good locations • Food centres • Cheap hostels/hotels
13. Finding lost bags @rkh_bag
14. Happy Customers
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!
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
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
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)
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
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!
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