PgSearch builds named scopes that take advantage of PostgreSQL’s full text search
Read the blog post introducing PgSearch at bit.ly/pg_search
gem install pg_search
In Gemfile
gem 'pg_search'
The newest versions of PgSearch no longer support Rails 2. However, the 0.2 series still works. It’s not actively maintained, but submissions are welcome for backports and bugfixes.
The 0.2 branch lives at github.com/Casecommons/pg_search/tree/0.2-stable
In addition to installing and requiring the gem, you may want to include the PgSearch rake tasks in your Rakefile:
load "pg_search/tasks.rb"
To add PgSearch to an ActiveRecord model, simply include the PgSearch module.
class Shape < ActiveRecord::Base include PgSearch end
pg_search supports two different techniques for searching, multi-search and search scopes.
The first technique is multi-search, in which records of many different Active Record classes can be mixed together into one global search index across your entire application. Most sites that want to support a generic search page will want to use this feature.
The other technique is search scopes, which allow you to do more advanced searching against only one Active Record class. This is more useful for building things like autocompleters or filtering a list of items in a faceted search.
Before using multi-search, you must generate and run a migration to create the pg_search_documents database table.
$ rake pg_search:migration:multisearch $ rake db:migrate
To add a model to the global search index for your application, call multisearchable in its class definition.
class EpicPoem < ActiveRecord::Base include PgSearch multisearchable :against => [:title, :author] end class Flower < ActiveRecord::Base include PgSearch multisearchable :against => :color end
Whenever a record is created, updated, or destroyed, an Active Record callback will fire, leading to the creation of a corresponding PgSearch::Document record in the pg_search_documents table. The :against option can be one or several methods which will be called on the record to generate its search text.
Two associations are built automatically. On the original record, there is a has_one :pg_search_document association pointing to the PgSearch::Document record, and on the PgSearch::Document record there is a belongs_to :searchable polymorphic association pointing back to the original record.
odyssey = EpicPoem.create!(:title => "Odyssey", :author => "Homer") search_document = odyssey.pg_search_document #=> PgSearch::Document instance search_document.searchable #=> #<EpicPoem id: 1, title: "Odyssey", author: "Homer">
To fetch the PgSearch::Document entries for all of the records that match a given query, use PgSearch.multisearch.
odyssey = EpicPoem.create!(:title => "Odyssey", :author => "Homer") rose = Flower.create!(:color => "Red") PgSearch.multisearch("Homer") #=> [#<PgSearch::Document searchable: odyssey>] PgSearch.multisearch("Red") #=> [#<PgSearch::Document searchable: rose>]
PgSearch.multisearch returns an ActiveRecord::Relation, just like scopes do, so you can chain scope calls to the end. This works with gems like Kaminari that add scope methods. Just like with regular scopes, the database will only receive SQL requests when necessary.
PgSearch.multisearch("Bertha").limit(10) PgSearch.multisearch("Juggler").where(:searchable_type => "Occupation") PgSearch.multisearch("Alamo").page(3).per_page(30) PgSearch.mulitsearch("Diagonal").find_each do |document| puts document.searchable.updated_at end
PgSearch.multisearch can be configured using the same options as ‘pg_search_scope` (explained in more detail below). Just set the PgSearch.multisearchable_options in an initializer:
PgSearch.multisearchable_options = { :using => [:tsearch, :trigram], :ignoring => :accents }
If you change the :against option on a class, add multisearchable to a class that already has records in the database, or remove multisearchable from a class in order to remove it from the index, you will find that the pg_search_documents table could become out-of-sync with the actual records in your other tables.
The index can also become out-of-sync if you ever modify records in a way that does not trigger Active Record callbacks. For instance, the #update_attribute instance method and the .update_all class method both skip callbacks and directly modify the database.
To remove all of the documents for a given class, you can simply delete all of the PgSearch::Document records.
PgSearch::Document.delete_all(:searchable_type => "Animal")
Run this Rake task to regenerate all of the documents for a given class.
$ rake pg_search:multisearch:rebuild MODEL=BlogPost
Currently this is only supported for :against methods that directly map to Active Record attributes. Until that is fixed, you could also manually rebuild all of the documents.
PgSearch::Document.delete_all(:searchable_type => "Ingredient") Ingredient.find_each { |record| record.update_pg_search_document }
If you have a large bulk operation to perform, such as importing a lot of records from an external source, you might want to speed things up by turning off indexing temporarily. You could then use one of the techniques above to rebuild the search documents off-line.
PgSearch.disable_multisearch do Movie.import_from_xml_file(File.open("movies.xml")) end
You can use pg_search_scope to build a search scope. The first parameter is a scope name, and the second parameter is an options hash. The only required option is :against, which tells pg_search_scope which column or columns to search against.
To search against a column, pass a symbol as the :against option.
class BlogPost < ActiveRecord::Base include PgSearch pg_search_scope :search_by_title, :against => :title end
We now have an ActiveRecord scope named search_by_title on our BlogPost model. It takes one parameter, a search query string.
BlogPost.create!(:title => "Recent Developments in the World of Pastrami") BlogPost.create!(:title => "Prosciutto and You: A Retrospective") BlogPost.search_by_title("pastrami") # => [#<BlogPost id: 2, title: "Recent Developments in the World of Pastrami">]
Just pass an Array if you’d like to search more than one column.
class Person < ActiveRecord::Base include PgSearch pg_search_scope :search_by_full_name, :against => [:first_name, :last_name] end
Now our search query can match either or both of the columns.
person_1 = Person.create!(:first_name => "Grant", :last_name => "Hill") person_2 = Person.create!(:first_name => "Hugh", :last_name => "Grant") Person.search_by_full_name("Grant") # => [person_1, person_2] Person.search_by_full_name("Grant Hill") # => [person_1]
Just like with Active Record named scopes, you can pass in a Proc object that returns a hash of options. For instance, the following scope takes a parameter that dynamically chooses which column to search against.
Important: The returned hash must include a :query key. Its value does not necessary have to be dynamic. You could choose to hard-code it to a specific value if you wanted.
class Person < ActiveRecord::Base include PgSearch pg_search_scope :search_by_name, lambda do |name_part, query| raise ArgumentError unless [:first, :last].include?(name_part) { :against => name_part, :query => query } end end person_1 = Person.create!(:first_name => "Grant", :last_name => "Hill") person_2 = Person.create!(:first_name => "Hugh", :last_name => "Grant") Person.search_by_name :first, "Grant" # => [person_1] Person.search_by_name :last, "Grant" # => [person_2]
It is possible to search columns on associated models. Note that if you do this, it will be impossible to speed up searches with database indexes. However, it is supported as a quick way to try out cross-model searching.
In PostgreSQL 8.3 and earlier, you must install a utility function into your database. To generate a migration for this, run:
$ rake pg_search:migration:associated_against
This migration is safe to run against newer versions of PostgreSQL as well. It will essentially do nothing.
You can pass a Hash into the :associated_against option to set up searching through associations. The keys are the names of the associations and the value works just like an :against option for the other model. Right now, searching deeper than one association away is not supported. You can work around this by setting up a series of :through associations to point all the way through.
class Cracker < ActiveRecord::Base has_many :cheeses end class Cheese < ActiveRecord::Base end class Salami < ActiveRecord::Base include PgSearch belongs_to :cracker has_many :cheeses, :through => :cracker pg_search_scope :tasty_search, :associated_against => { :cheeses => [:kind, :brand], :cracker => :kind } end salami_1 = Salami.create! salami_2 = Salami.create! salami_3 = Salami.create! limburger = Cheese.create!(:kind => "Limburger") brie = Cheese.create!(:kind => "Brie") pepper_jack = Cheese.create!(:kind => "Pepper Jack") Cracker.create!(:kind => "Black Pepper", :cheeses => [brie], :salami => salami_1) Cracker.create!(:kind => "Ritz", :cheeses => [limburger, pepper_jack], :salami => salami_2) Cracker.create!(:kind => "Graham", :cheeses => [limburger], :salami => salami_3) Salami.tasty_search("pepper") # => [salami_1, salami_2]
By default, pg_search_scope uses the built-in PostgreSQL text search. If you pass the :using option to pg_search_scope, you can choose alternative search techniques.
class Beer < ActiveRecord::Base include PgSearch pg_search_scope :search_name, :against => :name, :using => [:tsearch, :trigram, :dmetaphone] end
The currently implemented features are
-
:tsearch - Full text search (built-in with 8.3 and later, available as a contrib package for some earlier versions)
-
:trigram - Trigram search, which requires the trigram contrib package
-
:dmetaphone - Double Metaphone search, which requires the fuzzystrmatch contrib package
PostgreSQL’s built-in full text search supports weighting, prefix searches, and stemming in multiple languages.
Each searchable column can be given a weight of “A”, “B”, “C”, or “D”. Columns with earlier letters are weighted higher than those with later letters. So, in the following example, the title is the most important, followed by the subtitle, and finally the content.
class NewsArticle < ActiveRecord::Base include PgSearch pg_search_scope :search_full_text, :against => { :title => 'A', :subtitle => 'B', :content => 'C' } end
You can also pass the weights in as an array of arrays, or any other structure that responds to #each and yields either a single symbol or a symbol and a weight. If you omit the weight, a default will be used.
class NewsArticle < ActiveRecord::Base include PgSearch pg_search_scope :search_full_text, :against => [ [:title, 'A'], [:subtitle, 'B'], [:content, 'C'] ] end class NewsArticle < ActiveRecord::Base include PgSearch pg_search_scope :search_full_text, :against => [ [:title, 'A'], {:subtitle => 'B'}, :content ] end
PostgreSQL’s full text search matches on whole words by default. If you want to search for partial words, however, you can set :prefix to true. Since this is a :tsearch-specific option, you should pass it to :tsearch directly, as shown in the following example.
class Superhero < ActiveRecord::Base include PgSearch pg_search_scope :whose_name_starts_with, :against => :name, :using => { :tsearch => {:prefix => true} } end batman = Superhero.create :name => 'Batman' batgirl = Superhero.create :name => 'Batgirl' robin = Superhero.create :name => 'Robin' Superhero.whose_name_starts_with("Bat") # => [batman, batgirl]
PostgreSQL full text search also support multiple dictionaries for stemming. You can learn more about how dictionaries work by reading the PostgreSQL documention. If you use one of the language dictionaries, such as “english”, then variants of words (e.g. “jumping” and “jumped”) will match each other. If you don’t want stemming, you should pick the “simple” dictionary which does not do any stemming. If you don’t specify a dictionary, the “simple” dictionary will be used.
class BoringTweet < ActiveRecord::Base include PgSearch pg_search_scope :kinda_matching, :against => :text, :using => { :tsearch => {:dictionary => "english"} } pg_search_scope :literally_matching, :against => :text, :using => { :tsearch => {:dictionary => "simple"} } end sleepy = BoringTweet.create! :text => "I snoozed my alarm for fourteen hours today. I bet I can beat that tomorrow! #sleepy" sleeping = BoringTweet.create! :text => "You know what I like? Sleeping. That's what. #enjoyment" sleeper = BoringTweet.create! :text => "Have you seen Woody Allen's movie entitled Sleeper? Me neither. #boycott" BoringTweet.kinda_matching("sleeping") # => [sleepy, sleeping, sleeper] BoringTweet.literally_matching("sleeping") # => [sleeping]
PostgreSQL supports multiple algorithms for ranking results against queries. For instance, you might want to consider overall document size or the distance between multiple search terms in the original text. This option takes an integer, which is passed directly to PostgreSQL. According to the latest PostgreSQL documentation, the supported algorithms are:
0 (the default) ignores the document length 1 divides the rank by 1 + the logarithm of the document length 2 divides the rank by the document length 4 divides the rank by the mean harmonic distance between extents 8 divides the rank by the number of unique words in document 16 divides the rank by 1 + the logarithm of the number of unique words in document 32 divides the rank by itself + 1
This integer is a bitmask, so if you want to combine algorithms, you can add their numbers together. (e.g. to use algorithms 1, 8, and 32, you would pass 1 + 8 + 32 = 41)
class BigLongDocument < ActiveRecord::Base
include PgSearch
pg_search_scope :regular_search,
:against => :text
pg_search_scope :short_search,
:against => :text,
:using => {
:tsearch => {:normalization => 2}
}
long = BigLongDocument.create!(:text => "Four score and twenty years ago")
short = BigLongDocument.create!(:text => "Four score")
BigLongDocument.regular_search("four score") #=> [long, short]
BigLongDocument.short_search("four score") #=> [short, long]
Setting this attribute to true will perform a search which will return all models containing any word in the search terms.
class Number < ActiveRecord::Base include PgSearch pg_search_scope :search_any_word, :against => :text, :using => { :tsearch => {:any_word => true} } pg_search_scope :search_all_words, :against => :text end one = Number.create! :text => 'one' two = Number.create! :text => 'two' three = Number.create! :text => 'three' Number.search_any_word('one two three') # => [one, two, three] Number.search_all_words('one two three') # => []
Double Metaphone is an algorithm for matching words that sound alike even if they are spelled very differently. For example, “Geoff” and “Jeff” sound identical and thus match. Currently, this is not a true double-metaphone, as only the first metaphone is used for searching.
Double Metaphone support is currently available as part of the fuzzystrmatch contrib package that must be installed before this feature can be used. In addition to the contrib package, you must install a utility function into your database. To generate a migration for this, run:
$ rake pg_search:migration:dmetaphone
The following example shows how to use :dmetaphone.
class Word < ActiveRecord::Base include PgSearch pg_search_scope :that_sounds_like, :against => :spelling, :using => :dmetaphone end four = Word.create! :spelling => 'four' far = Word.create! :spelling => 'far' fur = Word.create! :spelling => 'fur' five = Word.create! :spelling => 'five' Word.that_sounds_like("fir") # => [four, far, fur]
Trigram search works by counting how many three-letter substrings (or “trigrams”) match between the query and the text. For example, the string “Lorem ipsum” can be split into the following trigrams:
[" Lo", "Lor", "ore", "rem", "em ", "m i", " ip", "ips", "psu", "sum", "um ", "m "]
Trigram search has some ability to work even with typos and misspellings in the query or text.
Trigram support is currently available as part of the pg_trgm contrib package that must be installed before this feature can be used.
class Website < ActiveRecord::Base include PgSearch pg_search_scope :kinda_spelled_like, :against => :name, :using => :trigram end yahooo = Website.create! :name => "Yahooo!" yohoo = Website.create! :name => "Yohoo!" gogle = Website.create! :name => "Gogle" facebook = Website.create! :name => "Facebook" Website.kinda_spelled_like("Yahoo!") # => [yahooo, yohoo]
Most of the time you will want to ignore accent marks when searching. This makes it possible to find words like “piñata” when searching with the query “pinata”. If you set a pg_search_scope to ignore accents, it will ignore accents in both the searchable text and the query terms.
Ignoring accents uses the unaccent contrib package that must be installed before this feature can be used.
class SpanishQuestion < ActiveRecord::Base include PgSearch pg_search_scope :gringo_search, :against => :word, :ignoring => :accents end what = SpanishQuestion.create(:word => "Qué") how_many = SpanishQuestion.create(:word => "Cuánto") how = SpanishQuestion.create(:word => "Cómo") SpanishQuestion.gringo_search("Que") # => [what] SpanishQuestion.gringo_search("Cüåñtô") # => [how_many]
PostgreSQL allows you the ability to search against a column with type tsvector instead of using an expression; this speeds up searching dramatically as it offloads creation of the tsvector that the tsquery is evaluated against.
To use this functionality you’ll need to do a few things:
-
Create a column of type tsvector that you’d like to search against. If you want to search using multiple search methods, for example tsearch and dmetaphone, you’ll need a column for each.
-
Create a trigger function that will update the column(s) using the expression appropriate for that type of search. See: www.postgresql.org/docs/current/static/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS
-
Should you have any pre-existing data in the table, update the newly-created tsvector columns with the expression that your trigger function uses.
-
Add the option to pg_search_scope, e.g:
pg_search_scope :fast_content_search, :against => :content, :using => { dmetaphone: { tsvector_column: 'tsvector_content_dmetaphone' }, tsearch: { dictionary: 'english', tsvector_column: 'tsvector_content_tsearch' } trigram: {} # trigram does not use tsvectors }
Please note that the :against column is only used when the tsvector_column is not present for the search type.
-
ActiveRecord 3
-
PostgreSQL
-
PostgreSQL contrib packages for certain features
PgSearch would not have been possible without inspiration from texticle. Thanks to Aaron Patterson!
Welcomed! Feel free to join and contribute to our public Pivotal Tracker project where we manage new feature ideas and bugs.
We also have a Google Group for discussing pg_search and other Case Commons open source projects.
MIT