pg_search

DESCRIPTION

PgSearch builds named scopes that take advantage of PostgreSQL's full text search.

Read the blog post introducing PgSearch at https://tanzu.vmware.com/content/blog/pg-search-how-i-learned-to-stop-worrying-and-love-postgresql-full-text-search

REQUIREMENTS

Ruby 3.0+
Active Record 6.1+
PostgreSQL 9.2+
PostgreSQL extensions for certain features

INSTALL

$ gem install pg_search

or add this line to your Gemfile:

gem 'pg_search'

Non-Rails projects

In addition to installing and requiring the gem, you may want to include the PgSearch rake tasks in your Rakefile. This isn't necessary for Rails projects, which gain the Rake tasks via a Railtie.

load "pg_search/tasks.rb"

USAGE

To add PgSearch to an Active Record model, simply include the PgSearch module.

class Shape < ActiveRecord::Base
  include PgSearch::Model
end

Multi-search vs. search scopes
Multi-search
pg_search_scope
Searching using different search features
Limiting Fields When Combining Features
Ignoring accent marks
Using tsvector columns
- Combining multiple tsvectors
Configuring ranking and ordering

Multi-search vs. search scopes

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.

Multi-search

Setup

Before using multi-search, you must generate and run a migration to create the pg_search_documents database table.

$ rails g pg_search:migration:multisearch
$ rake db:migrate

multisearchable

To add a model to the global search index for your application, call multisearchable in its class definition.

class EpicPoem < ActiveRecord::Base
  include PgSearch::Model
  multisearchable against: [:title, :author]
end

class Flower < ActiveRecord::Base
  include PgSearch::Model
  multisearchable against: :color
end

If this model already has existing records, you will need to reindex this model to get existing records into the pg_search_documents table. See the rebuild task below.

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.

You can also pass a Proc or method name to call to determine whether or not a particular record should be included.

class Convertible < ActiveRecord::Base
  include PgSearch::Model
  multisearchable against: [:make, :model],
                  if: :available_in_red?
end

class Jalopy < ActiveRecord::Base
  include PgSearch::Model
  multisearchable against: [:make, :model],
                  if: lambda { |record| record.model_year > 1970 }
end

Note that the Proc or method name is called in an after_save hook. This means that you should be careful when using Time or other objects. In the following example, if the record was last saved before the published_at timestamp, it won't get listed in global search at all until it is touched again after the timestamp.

class AntipatternExample
  include PgSearch::Model
  multisearchable against: [:contents],
                  if: :published?

  def published?
    published_at < Time.now
  end
end

problematic_record = AntipatternExample.create!(
  contents: "Using :if with a timestamp",
  published_at: 10.minutes.from_now
)

problematic_record.published?     # => false
PgSearch.multisearch("timestamp") # => No results

sleep 20.minutes

problematic_record.published?     # => true
PgSearch.multisearch("timestamp") # => No results

problematic_record.save!

problematic_record.published?     # => true
PgSearch.multisearch("timestamp") # => Includes problematic_record

More Options

Conditionally update pg_search_documents

You can also use the :update_if option to pass a Proc or method name to call to determine whether or not a particular record should be updated.

Note that the Proc or method name is called in an after_save hook, so if you are relying on ActiveRecord dirty flags use *_previously_changed?.

class Message < ActiveRecord::Base
  include PgSearch::Model
  multisearchable against: [:body],
                  update_if: :body_previously_changed?
end

Specify additional attributes to be saved on the pg_search_documents table

You can specify :additional_attributes to be saved within the pg_search_documents table. For example, perhaps you are indexing a book model and an article model and wanted to include the author_id.

First, we need to add a reference to author to the migration creating our pg_search_documents table.

  create_table :pg_search_documents do |t|
    t.text :content
    t.references :author, index: true
    t.belongs_to :searchable, polymorphic: true, index: true
    t.timestamps null: false
  end

Then, we can send in this additional attribute in a lambda

  multisearchable(
    against: [:title, :body],
    additional_attributes: -> (article) { { author_id: article.author_id } }
  )

This allows much faster searches without joins later on by doing something like:

PgSearch.multisearch(params['search']).where(author_id: 2)

NOTE: You must currently manually call record.update_pg_search_document for the additional attribute to be included in the pg_search_documents table

Multi-search associations

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">

Searching in the global search index

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>]

Chaining method calls onto the results

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(30)
PgSearch.multisearch("Diagonal").find_each do |document|
  puts document.searchable.updated_at
end
PgSearch.multisearch("Moro").reorder("").group(:searchable_type).count(:all)
PgSearch.multisearch("Square").includes(:searchable)

Configuring multi-search

PgSearch.multisearch can be configured using the same options as pg_search_scope (explained in more detail below). Just set the PgSearch.multisearch_options in an initializer:

PgSearch.multisearch_options = {
  using: [:tsearch, :trigram],
  ignoring: :accents
}

Rebuilding search documents for a given class

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_by(searchable_type: "Animal")

To regenerate the documents for a given class, run:

PgSearch::Multisearch.rebuild(Product)

The rebuild method will delete all the documents for the given class before regenerating them. In some situations this may not be desirable, such as when you're using single-table inheritance and searchable_type is your base class. You can prevent rebuild from deleting your records like so:

PgSearch::Multisearch.rebuild(Product, clean_up: false)

rebuild runs inside a single transaction. To run outside of a transaction, you can pass transactional: false like so:

PgSearch::Multisearch.rebuild(Product, transactional: false)

Rebuild is also available as a Rake task, for convenience.

$ rake pg_search:multisearch:rebuild[BlogPost]

A second optional argument can be passed to specify the PostgreSQL schema search path to use, for multi-tenant databases that have multiple pg_search_documents tables. The following will set the schema search path to "my_schema" before reindexing.

$ rake pg_search:multisearch:rebuild[BlogPost,my_schema]

For models that are multisearchable :against methods that directly map to Active Record attributes, an efficient single SQL statement is run to update the pg_search_documents table all at once. However, if you call any dynamic methods in :against then update_pg_search_document will be called on the individual records being indexed in batches.

You can also provide a custom implementation for rebuilding the documents by adding a class method called rebuild_pg_search_documents to your model.

class Movie < ActiveRecord::Base
  belongs_to :director

  def director_name
    director.name
  end

  multisearchable against: [:name, :director_name]

  # Naive approach
  def self.rebuild_pg_search_documents
    find_each { |record| record.update_pg_search_document }
  end

  # More sophisticated approach
  def self.rebuild_pg_search_documents
    connection.execute <<~SQL.squish
     INSERT INTO pg_search_documents (searchable_type, searchable_id, content, created_at, updated_at)
       SELECT 'Movie' AS searchable_type,
              movies.id AS searchable_id,
              CONCAT_WS(' ', movies.name, directors.name) AS content,
              now() AS created_at,
              now() AS updated_at
       FROM movies
       LEFT JOIN directors
         ON directors.id = movies.director_id
    SQL
  end
end

Note: If using PostgreSQL before 9.1, replace the CONCAT_WS() function call with double-pipe concatenation, eg. (movies.name || ' ' || directors.name). However, now be aware that if any of the joined values is NULL then the final content value will also be NULL, whereas CONCAT_WS() will selectively ignore NULL values.

Disabling multi-search indexing temporarily

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

pg_search_scope

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.

Searching against one column

To search against a column, pass a symbol as the :against option.

class BlogPost < ActiveRecord::Base
  include PgSearch::Model
  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">]

Searching against multiple columns

Just pass an Array if you'd like to search more than one column.

class Person < ActiveRecord::Base
  include PgSearch::Model
  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]

Dynamic search scopes

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