x
 6665d421cfc8686c2d615c8769fd5f6204d8ea1d564d3dc6d5 pimgpsh fullsize distr

PG SEARCH

11 months ago by Andreea Popescu

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")
Did you like this post? Share it with your friends!