Tweet
Tweet

Slide 1

Slide 1 text

ActiveRecord Scopes & Arel Jacob Swanner

Slide 2

Slide 2 text

ActiveRecord Scopes & Arel ‣ Before Rails 2.1 Where we came from

Slide 3

Slide 3 text

ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find methods with options hash Where we came from

Slide 4

Slide 4 text

ActiveRecord Scopes & Arel ‣ Before Rails 2.1 • Find methods with options hash • No built-in method of options reuse Where we came from

Slide 5

Slide 5 text

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' })

Slide 6

Slide 6 text

ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 Where we came from

Slide 7

Slide 7 text

ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 • named_scope was introduced Where we came from

Slide 8

Slide 8 text

ActiveRecord Scopes & Arel ‣ Starting with Rails 2.1 • named_scope was introduced • Allowed for reuse of query options Where we came from

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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')

Slide 11

Slide 11 text

ActiveRecord Scopes & Arel ‣ Starting with Rails 3 Where we came from

Slide 12

Slide 12 text

ActiveRecord Scopes & Arel ‣ Starting with Rails 3 • named_scope changed to scope Where we came from

Slide 13

Slide 13 text

ActiveRecord Scopes & Arel ‣ Starting with Rails 3 • named_scope changed to scope • New ActiveRecord query methods Where we came from

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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')

Slide 16

Slide 16 text

ActiveRecord Scopes & Arel Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end

Slide 17

Slide 17 text

ActiveRecord Scopes & Arel ‣ Works great, ship it! Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name LIKE ?', "%#{name}%") end end

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

ActiveRecord Scopes & Arel ‣ The fix Problem Scenario class Author < ActiveRecord::Base def self.name_contains(name) where('name ILIKE ?', "%#{name}%") end end

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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?

Slide 25

Slide 25 text

ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use Arel

Slide 26

Slide 26 text

ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use Arel • Relation Algebra library

Slide 27

Slide 27 text

ActiveRecord Scopes & Arel Problem Scenario ‣ Possible solution: Use Arel • Relation Algebra library • Used by ActiveRecord internally

Slide 28

Slide 28 text

ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy'))

Slide 29

Slide 29 text

ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) Table to query

Slide 30

Slide 30 text

ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name] Field to query on

Slide 31

Slide 31 text

ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name].eq( ) Operator to use

Slide 32

Slide 32 text

ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors[:name].eq('amy') Value to search for

Slide 33

Slide 33 text

ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy'))

Slide 34

Slide 34 text

ActiveRecord Scopes & Arel Arel Primer authors = Arel::Table.new(:authors) authors.where(authors[:name].eq('amy')) # SELECT * FROM authors WHERE authors.name = 'amy'

Slide 35

Slide 35 text

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'

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando', name: 'Bob') # SELECT * FROM authors WHERE authors.city = 'Orlando' AND authors.name = 'Bob'

Slide 40

Slide 40 text

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’?

Slide 41

Slide 41 text

ActiveRecord Scopes & Arel OR Scenario Author.where(city: 'Orlando'). where('name = ? OR name = ?', 'Bob', 'Carol') ‣ SQL fragment option

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('name IN (?)', ['Bob', 'Carol']) ‣ Don’t query like this

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

ActiveRecord Scopes & Arel Better use of ActiveRecord Author.where('books_count BETWEEN ? and ?', 1, 5) ‣ Don’t query like this

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

ActiveRecord Scopes & Arel Available Arel Predicates ‣ eq ‣ not_eq ‣ in ‣ not_in ‣ gteq ‣ gt ‣ lteq ‣ lt ‣ matches ‣ does_not_match Can append *_all & *_any

Slide 54

Slide 54 text

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' }

Slide 55

Slide 55 text

Thank You Jacob Swanner @jswanner Envy Labs