pg_search V2
class Variant < AR::B
include PgSearch
pg_search_scope(:autocomplete,
against: [:name, :sku, :barcode],
using: {tsearch: {prefix: true}}
)
###
end
Slide 19
Slide 19 text
pg_search V2
class Variant < AR::B
include PgSearch
pg_search_scope(:autocomplete,
against: [:name, :sku, :barcode, :description],
using: {tsearch: {prefix: true}}
)
###
end
Slide 20
Slide 20 text
pg_search V2
class Variant < AR::B
include PgSearch
pg_search_scope(:autocomplete,
against: [:name, :sku, :barcode, :description],
associated_against: {product: :name},
using: {tsearch: {prefix: true}}
)
###
end
Slide 21
Slide 21 text
No content
Slide 22
Slide 22 text
No content
Slide 23
Slide 23 text
No content
Slide 24
Slide 24 text
Dang!
Slide 25
Slide 25 text
I
but
Slide 26
Slide 26 text
No content
Slide 27
Slide 27 text
What are the options?
- Build it yourself
- tsvector column (But this requires SQL)
Slide 28
Slide 28 text
Build it yourself
class Variant < AR::B
include PgSearch
def self.autocomplete(term)
match = “%#{term}%”
self.where(‘name ilike ? or sku ilike ? or
description ilike ? or barcode ilike ?’,
match, match, match, match)
end
###
end
Slide 29
Slide 29 text
Build it yourself
class Variant < AR::B
include PgSearch
def self.autocomplete(term)
match = “%#{term}%”
self.joins(:product)
.where(‘variants.name ilike ? or sku ilike ? or
description ilike ? or barcode ilike ? or products.name ilike
?’, match, match, match, match, match)
end
###
end
Slide 30
Slide 30 text
Bonus - Use AREL
def self.autocomplete(term)
match = “%#{term}%”
search_arel = arel_table[:name].matches(match).or(
arel_table[:sku].matches(match).or(
arel_table[:description].matches(match).or(
arel_table[:barcode].matches(match).or(
Product.arel_table[:name].matches(match)
)
)
)
)
self.joins(:product).where(search_arel)
end
What are the options?
- Build it yourself
- tsvector column (But this requires SQL)
Slide 33
Slide 33 text
tsvector
“A tsvector value is a sorted list of distinct
lexemes, which are words that have been
normalized to make different variants of the
same word look alike.”
- Postgres manual - http://www.postgresql.org/docs/9.2/static/datatype-textsearch.html
Slide 34
Slide 34 text
tsvector
def change
add_column :variants, :search, :tsvector
end
class Variant < AR::B
include PgSearch
pg_search_scope(:autocomplete,
against: :search,
using: {tsearch: {prefix: true}}
)
end
Slide 35
Slide 35 text
No content
Slide 36
Slide 36 text
No content
Slide 37
Slide 37 text
coalesce()
...returns the first of its arguments that is not
null…
coalesce(null, ‘’) #=> ‘’
Make sure we don’t have any pesky nulls
Slide 38
Slide 38 text
setweight()
...used to label the entries of a tsvector with a
given weight, where a weight is one of the
letters A, B, C, or D...
setweight(to_tsvector(name), 'A')
Slide 39
Slide 39 text
to_tsvector()
...parses a textual document into tokens,
reduces the tokens to lexemes, and returns a
tsvector which lists the lexemes together with
their positions in the document...
to_tsvector(‘simple’, name)
Slide 40
Slide 40 text
to_tsvector()
to_tsvector(‘simple’,’a fat cat sat on a mat’)
#=> 'a':1,6 'cat':3 'fat':2 'mat':7 'on':5 'sat':4
to_tsvector(‘english’,’a fat cat sat on a mat’)
#=> 'cat':3 'fat':2 'mat':7 'sat':4"
Slide 41
Slide 41 text
Product has_many Variants
Slide 42
Slide 42 text
Product has_many Variants
Arbitrary no-op to fire Variant
trigger
Slide 43
Slide 43 text
Product has_many Variants
AFTER not BEFORE
Slide 44
Slide 44 text
Seed the :search Column
Slide 45
Slide 45 text
Uh-Oh
AFTER not BEFORE
Slide 46
Slide 46 text
schema.rb doesn’t know about triggers
application.rb
- config.active_record.schema_format = :sql
But then you have to deal with even
more merge conflicts than schema.rb