Share
Embed
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
Transcript
63 slides
-
ActiveRecord Scopes & Arel ‣ Before Rails 2.1 Where we
came from Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find
methods with options hash Where we came from Tuesday, June 5, 12 -
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 -
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 -
ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 Where
we came from Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 •
named_scope was introduced Where we came from Tuesday, June 5, 12 -
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 -
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 -
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 -
ActiveRecord Scopes & Arel ‣ Starting with Rails 3 Where
we came from Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel ‣ Starting with Rails 3 •
named_scope changed to scope Where we came from Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel ‣ Starting with Rails 3 •
named_scope changed to scope • New ActiveRecord query methods Where we came from Tuesday, June 5, 12 -
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 -
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 -
ActiveRecord Scopes & Arel Problem Scenario class Author < ActiveRecord::Base
def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end Tuesday, June 5, 12 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use
Arel Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use
Arel • Relation Algebra library Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use
Arel • Relation Algebra library • Used by ActiveRecord internally Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) Table
to query Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name]
Field to query on Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name].eq(
) Operator to use Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name].eq('amy')
Value to search for Tuesday, June 5, 12 -
ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy'))
# SELECT * FROM authors WHERE authors.name = 'amy' Tuesday, June 5, 12 -
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 -
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 -
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 -
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 -
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 -
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 -
ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando'). where('name =
? OR name = ?', 'Bob', 'Carol') ‣ SQL fragment option Tuesday, June 5, 12 -
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 -
ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').order(:created_at) ‣ Does
not produce the SQL you think it does Tuesday, June 5, 12 -
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 -
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 -
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 -
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 -
ActiveRecord Scopes & Arel Subtle Gotchas Author.where(name: 'Bob').select(:name) ‣ Does
not produce the SQL you think it does Tuesday, June 5, 12 -
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 -
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 -
ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('name IN
(?)', ['Bob', 'Carol']) ‣ Don’t query like this Tuesday, June 5, 12 -
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 -
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 -
ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('books_count BETWEEN
? and ?', 1, 5) ‣ Don’t query like this Tuesday, June 5, 12 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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