ActiveRecord Scopes & Arel

Scopes are a great way of encapsulating query logic in a granular, reusable way. This talk will cover some techniques you can use to keep those scopes as composable and portable as possible. We’ll cover how to use Arel directly, while avoiding the common practice of using SQL fragments, and show you how this can make your scopes more reusable, while at the same time preventing you from using database vendor specific operators, such as ILIKE.

Transcript

1. ActiveRecord Scopes & Arel Jacob Swanner
2. ActiveRecord Scopes & Arel ‣ Before Rails 2.1 Where we came from
3. ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find methods with options hash Where we came from
4. ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find methods with options hash • No built-in method of options reuse Where we came from
5. ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find methods with options hash • No built-in method of options reuse Where we came from Author.find(:first, {:conditions => ['name = ?', 'Bob']}) Book.find(:all, { :conditions => ['title LIKE ?', '%foo%'], :order => 'title' })
6. ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 Where we came from
7. ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 • named_scope was introduced Where we came from
8. ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 • named_scope was introduced • Allowed for reuse of query options Where we came from
9. ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 • named_scope was introduced • Allowed for reuse of query options • Allowed for composition (chaining) Where we came from
10. ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 • named_scope was introduced • Allowed for reuse of query options • Allowed for composition (chaining) Where we came from class Author < ActiveRecord::Base named_scope :published, :conditions => 'books_count > 0' named_scope :by_first_name, lambda { |name| {:conditions => ['first_name = ?', name]} } end Author.published.by_first_name('Bob')
11. ActiveRecord Scopes & Arel ‣ Starting with Rails 3 Where we came from
12. ActiveRecord Scopes & Arel ‣ Starting with Rails 3 • named_scope changed to scope Where we came from
13. ActiveRecord Scopes & Arel ‣ Starting with Rails 3 • named_scope changed to scope • New ActiveRecord query methods Where we came from
14. ActiveRecord Scopes & Arel ‣ Starting with Rails 3 • named_scope changed to scope • New ActiveRecord query methods • Composibility on all query methods Where we came from
15. ActiveRecord Scopes & Arel ‣ Starting with Rails 3 • named_scope changed to scope • New ActiveRecord query methods • Composibility on all query methods Where we came from class Author < ActiveRecord::Base scope :published, where('books_count > 0') scope :by_first_name, lambda { |name| where(first_name: name) } end Author.published.by_first_name('Bob')
16. ActiveRecord Scopes & Arel Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end
17. ActiveRecord Scopes & Arel ‣ Works great, ship it! Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end
18. ActiveRecord Scopes & Arel ‣ Works great, ship it! ‣ Doesn’t work the same on Heroku, WTF?! Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end
19. ActiveRecord Scopes & Arel ‣ Works great, ship it! ‣ Doesn’t work the same on Heroku, WTF?! ‣ Doh! Heroku uses PostgreSQL Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end
20. ActiveRecord Scopes & Arel ‣ Works great, ship it! ‣ Doesn’t work the same on Heroku, WTF?! ‣ Doh! Heroku uses PostgreSQL ‣ LIKE is case-sensitive in PostgreSQL Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end
21. ActiveRecord Scopes & Arel ‣ The fix Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name ILIKE ?', "%#{name}%") end end
22. ActiveRecord Scopes & Arel ‣ The fix Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name ILIKE ?', "%#{name}%") end end ‣ Doh! No longer works on SQLite
23. ActiveRecord Scopes & Arel ‣ The fix Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name ILIKE ?', "%#{name}%") end end ‣ Doh! No longer works on SQLite ‣ Switch every environment to PostgreSQL
24. ActiveRecord Scopes & Arel ‣ The fix Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name ILIKE ?', "%#{name}%") end end ‣ Doh! No longer works on SQLite ‣ Switch every environment to PostgreSQL • What if you are not writing an app, but a library?
25. ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use Arel
26. ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use Arel • Relation Algebra library
27. ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use Arel • Relation Algebra library • Used by ActiveRecord internally
28. ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy'))
29. ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) Table to query
30. ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name] Field to query on
31. ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name].eq( ) Operator to use
32. ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name].eq('amy') Value to search for
33. ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy'))
34. ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy')) # SELECT * FROM authors WHERE authors.name = 'amy'
35. ActiveRecord Scopes & Arel ‣ Getting Arel::Table of ActiveRecord model Arel Primer authors = Author.arel_table authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy')) # SELECT * FROM authors WHERE authors.name = 'amy'
36. ActiveRecord Scopes & Arel Problem Solution class Author < ActiveRecord::Base def self.name_contains(name) where(self.arel_table[:name].matches("%#{name}%")) end end
37. ActiveRecord Scopes & Arel Problem Solution class Author < ActiveRecord::Base def self.name_contains(name) where(self.arel_table[:name].matches("%#{name}%")) end end • PostgreSQL: uses ILIKE
38. ActiveRecord Scopes & Arel Problem Solution class Author < ActiveRecord::Base def self.name_contains(name) where(self.arel_table[:name].matches("%#{name}%")) end end • PostgreSQL: uses ILIKE • Others: uses LIKE
39. ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando', name: 'Bob') # SELECT * FROM authors WHERE authors.city = 'Orlando' AND authors.name = 'Bob'
40. ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando', name: 'Bob') # SELECT * FROM authors WHERE authors.city = 'Orlando' AND authors.name = 'Bob' • But what about ‘Bob’ or ‘Carol’?
41. ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando'). where('name = ? OR name = ?', 'Bob', 'Carol') ‣ SQL fragment option
42. ActiveRecord Scopes & Arel ‣ Arel option OR Scenario Author.where(city: 'Orlando'). where('name = ? OR name = ?', 'Bob', 'Carol') name = Author.arel_table[:name] Author.where(city: 'Orlando'). where(name.eq('Bob').or(name.eq('Carol'))) ‣ SQL fragment option
43. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('name IN (?)', ['Bob', 'Carol']) ‣ Don’t query like this
44. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('name IN (?)', ['Bob', 'Carol']) ‣ Don’t query like this name = Author.arel_table[:name] Author.where(name.in(['Bob', 'Carol'])) ‣ Don’t query like this either
45. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('name IN (?)', ['Bob', 'Carol']) ‣ Don’t query like this name = Author.arel_table[:name] Author.where(name.in(['Bob', 'Carol'])) ‣ Don’t query like this either Author.where(name: ['Bob', 'Carol']) ‣ Array values invoke IN operator
46. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('books_count BETWEEN ? and ?', 1, 5) ‣ Don’t query like this
47. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('books_count BETWEEN ? and ?', 1, 5) ‣ Don’t query like this books_count = Author.arel_table[:books_count] Author.where(books_count.in(1..5)) ‣ Don’t query like this either
48. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('books_count BETWEEN ? and ?', 1, 5) ‣ Don’t query like this books_count = Author.arel_table[:books_count] Author.where(books_count.in(1..5)) ‣ Don’t query like this either Author.where(books_count: 1..5) ‣ Range values invoke BETWEEN operator
49. ActiveRecord Scopes & Arel Opinionated use of ActiveRecord class Author < ActiveRecord::Base scope :by_name, lambda { |name| where('name = ?', name) } end class Book < ActiveRecord::Base belongs_to :author scope :by_name, lambda { |name| where('name = ?', name) } end Book.by_name('The Art of War'). joins(:author).merge(Author.by_name('Sun Tzu')) ‣ I don’t like SQL fragments
50. ActiveRecord Scopes & Arel Opinionated use of ActiveRecord class Author < ActiveRecord::Base scope :by_name, lambda { |name| where('name = ?', name) } end class Book < ActiveRecord::Base belongs_to :author scope :by_name, lambda { |name| where('name = ?', name) } end Book.by_name('The Art of War'). joins(:author).merge(Author.by_name('Sun Tzu')) ‣ I don’t like SQL fragments Ambiguous column name error
51. ActiveRecord Scopes & Arel Opinionated use of ActiveRecord class Author < ActiveRecord::Base scope :by_first_name, lambda { |name| where(first_name: name) } end ‣ I don’t like scopes with lambdas
52. ActiveRecord Scopes & Arel Opinionated use of ActiveRecord class Author < ActiveRecord::Base scope :by_first_name, lambda { |name| where(first_name: name) } end ‣ I don’t like scopes with lambdas class Author < ActiveRecord::Base def self.by_first_name(name) where(first_name: name) end end ‣ I prefer to use class methods
53. ActiveRecord Scopes & Arel Available Arel Predicates ‣ eq ‣ not_eq ‣ in ‣ not_in ‣ gteq ‣ gt ‣ lteq ‣ lt ‣ matches ‣ does_not_match Can append *_all & *_any
54. ActiveRecord Scopes & Arel Squeel Author.where('name LIKE ?', 'Bob') ‣ https://github.com/ernie/squeel ‣ All are equivalent: name = Author.arel_table[:name] Author.where(name.matches('Bob')) Author.where{ name =~ 'Bob' }
55. Thank You Jacob Swanner @jswanner Envy Labs