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

ActiveRecord Scopes & Arel [RailsWayCon Version]

ActiveRecord Scopes & Arel [RailsWayCon Version]

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.

jswanner

June 06, 2012
Tweet

More Decks by jswanner

Other Decks in Programming

Transcript

  1. ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find

    methods with options hash Where we came from Tuesday, June 5, 12
  2. ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find

    methods with options hash • No built-in method of options reuse Where we came from Tuesday, June 5, 12
  3. 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' }) Tuesday, June 5, 12
  4. ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 •

    named_scope was introduced Where we came from Tuesday, June 5, 12
  5. ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 •

    named_scope was introduced • Allowed for reuse of query options Where we came from Tuesday, June 5, 12
  6. 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 Tuesday, June 5, 12
  7. 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') Tuesday, June 5, 12
  8. ActiveRecord Scopes & Arel ‣ Starting with Rails 3 •

    named_scope changed to scope Where we came from Tuesday, June 5, 12
  9. ActiveRecord Scopes & Arel ‣ Starting with Rails 3 •

    named_scope changed to scope • New ActiveRecord query methods Where we came from Tuesday, June 5, 12
  10. 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 Tuesday, June 5, 12
  11. 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') Tuesday, June 5, 12
  12. ActiveRecord Scopes & Arel Problem Scenario class Author < ActiveRecord::Base

    def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end Tuesday, June 5, 12
  13. ActiveRecord Scopes & Arel ‣ Works great, ship it! Problem

    Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end Tuesday, June 5, 12
  14. 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 Tuesday, June 5, 12
  15. 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 Tuesday, June 5, 12
  16. 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 Tuesday, June 5, 12
  17. ActiveRecord Scopes & Arel ‣ The fix Problem Scenario class

    Author < ActiveRecord::Base def self.name_contains(name) where('name ILIKE ?', "%#{name}%") end end Tuesday, June 5, 12
  18. 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 Tuesday, June 5, 12
  19. 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 Tuesday, June 5, 12
  20. 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? Tuesday, June 5, 12
  21. ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use

    Arel • Relation Algebra library Tuesday, June 5, 12
  22. ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use

    Arel • Relation Algebra library • Used by ActiveRecord internally Tuesday, June 5, 12
  23. 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' Tuesday, June 5, 12
  24. ActiveRecord Scopes & Arel Problem Solution class Author < ActiveRecord::Base

    def self.name_contains(name) where(arel_table[:name].matches("%#{name}%")) end end Tuesday, June 5, 12
  25. ActiveRecord Scopes & Arel Problem Solution class Author < ActiveRecord::Base

    def self.name_contains(name) where(arel_table[:name].matches("%#{name}%")) end end • PostgreSQL: uses ILIKE Tuesday, June 5, 12
  26. ActiveRecord Scopes & Arel Problem Solution class Author < ActiveRecord::Base

    def self.name_contains(name) where(arel_table[:name].matches("%#{name}%")) end end • PostgreSQL: uses ILIKE • Others: uses LIKE Tuesday, June 5, 12
  27. ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando', name: 'Bob')

    # SELECT * FROM authors WHERE authors.city = 'Orlando' AND authors.name = 'Bob' Tuesday, June 5, 12
  28. 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’? Tuesday, June 5, 12
  29. ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando'). where('name =

    ? OR name = ?', 'Bob', 'Carol') ‣ SQL fragment option Tuesday, June 5, 12
  30. 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 Tuesday, June 5, 12
  31. ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').order(:created_at) ‣ Does

    not produce the SQL you think it does # SELECT authors.* FROM authors WHERE authors.name = 'Bob' ORDER BY created_at Tuesday, June 5, 12
  32. ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').order(:created_at) ‣ Does

    not produce the SQL you think it does # SELECT authors.* FROM authors WHERE authors.name = 'Bob' ORDER BY created_at Table prefix Tuesday, June 5, 12
  33. ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').order(:created_at) ‣ Does

    not produce the SQL you think it does # SELECT authors.* FROM authors WHERE authors.name = 'Bob' ORDER BY created_at Table prefix No table prefix Tuesday, June 5, 12
  34. ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').order(:created_at) # SELECT

    authors.* FROM authors WHERE authors.name = 'Bob' ORDER BY created_at ‣ Does not produce the SQL you think it does created_at = Author.arel_table[:created_at] Author.where(name: 'Bob').order(created_at) ‣ Does include table prefix Author.where(name: 'Bob').order('authors.created_at') ‣ Alternative Tuesday, June 5, 12
  35. ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').select(:name) ‣ Does

    not produce the SQL you think it does Tuesday, June 5, 12
  36. ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').select(:name) ‣ Does

    not produce the SQL you think it does # SELECT name FROM authors WHERE authors.name = 'Bob' Tuesday, June 5, 12
  37. ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').select(:name) ‣ Does

    not produce the SQL you think it does ‣ Same problem and same solution as order # SELECT name FROM authors WHERE authors.name = 'Bob' Tuesday, June 5, 12
  38. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('name IN

    (?)', ['Bob', 'Carol']) ‣ Don’t query like this Tuesday, June 5, 12
  39. 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 Tuesday, June 5, 12
  40. 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 Tuesday, June 5, 12
  41. ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('books_count BETWEEN

    ? and ?', 1, 5) ‣ Don’t query like this Tuesday, June 5, 12
  42. 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 Tuesday, June 5, 12
  43. 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 Tuesday, June 5, 12
  44. 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 Tuesday, June 5, 12
  45. 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 Tuesday, June 5, 12
  46. 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 Tuesday, June 5, 12
  47. 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 Tuesday, June 5, 12
  48. ActiveRecord Scopes & Arel Available Arel Predicates ‣ eq ‣

    not_eq ‣ in ‣ not_in ‣ gteq ‣ gt ‣ lteq ‣ lt ‣ matches ‣ does_not_match Can append *_all & *_any Tuesday, June 5, 12
  49. 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' } Tuesday, June 5, 12