PG SEARCH
Popup
PostgreSQL is a popular SQL database solution that works great with Active Record and supports full-text search. However, full-text search in PostgreSQL uses fairly complex SQL queries. To make this task easy we can take advantage of Active Record’s scopes using a gem called “pg_search”.
Setting up
To install pg_search run:
$ gem install pg_search
or add the following gem in your Gemfile and run ‘bundle install’:
gem ‘pg_search'
Usage
PgSearch supports two different techniques for searching, multi-search and search scopes. While search scopes allow you to do more advanced searching against only one Active Record class: multi-search can mix together records of many different Active Record classes into one global search index for your entire application.
Search Scopes
To setup a pg_search scope, add the following to your model:
class Author < ActiveRecord::Base
has_many :articles
class Article < ActiveRecord::Base
belongs_to :author
attr_accessible :body, :title
include PgSearch
pg_search_scope :my_search,
:against => :title
The first parameter is the scope name.
The second parameter is an option hash, :against, that specifies which columns to search against. We can pass only a attribute (as in the example above), or we can pass an array:
:against => [:title, :body]
We can also prioritize each searchable column by giving it a weight of A, B, C or D:
:against => {:title => 'A', :body => 'B'}
Earlier elements are weighted higher, so the title is the most important, followed by the article’s body.
It is possible to search columns on associated models by passing a Hash into the :associated_against option:
pg_search_scope :my_search,
:against => { :title => 'A', :body => 'B' },
:associated_against => { :author => :name }
By default, pg_search_scope uses the built-in PostgreSQL text search, but it also supports different search features such as:
- Full text search ( :tsearch )
- Trigram search ( :trigram )
- Double Metaphone search ( :dmetaphone )
which can be used by passing the :using option to pg_search_scope and adding the feature’s specific options:
:using => {:tsearch => { :dictionary => ‘english’,
:prefix => true,
:any_word => true}}
The example above contains some useful search options:
- :dictionary option enables stemming through multiple dictionaries ( by default PostgreSQL matches only the exact words in the search term ). For instance the “english" language dictionary will match to each other different variants of words:
Article.my_search(“jumping”) # matches: “jump”, “jumping”, “jumped”
- :prefix can be enabled to search for partial words:
Article.my_search(“bat”) # matches: “bat”, “batman”, “batmobile”
- :any_word option will return all models containing any word in the search terms. ( by default PostgreSQL returns only the models that matched all the words in the search term )
Multisearch
Before using multisearch, run the generator to create a migration for the multisearch result documents.
$ rails generate pg_search:migration:multisearch
$ rake db:migrate
Next we enable multisearch on all models we are interested in and specify the relevant attributes:
class Article < ActiveRecord::Base
attr_accessible :body, :title
include PgSearch
multisearchable :against => [:body, :title]
class Author < ActiveRecord::Base
attr_accessible :name
include PgSearch
multisearchable :against => [:name]
PgSearch.multisearch can be configured using the same options as pg_search:
pg_search_scope.
PgSearch.multisearch_options = {
:using => :tsearch
… }
To get the results just call the multi search with your search term:
results = PgSearch.multisearch("something")