Easily searching across an application’s data is a pervasive need. If you are lucky, you can get away with simple sorting or searching on a single column, but it is more likely that you need full text search across multiple models, all from a single search field.
There are many standalone services, some hosted and some not, offering full text search. We looked at a few of these, but decided that adding too much to our classes or having an external service running during tests weren’t things we wanted for this project.
Thanks to the power of Postgres’ full text search, rolling your own search isn’t too difficult.
If all you need is to search over a few models’ text and string fields, this approach is probably the simplest thing you can do.
We’ll need to construct a database view which presents a polymorphic relationship to the individual result and the text column being searched.
CREATE VIEW searches AS SELECT statuses.id AS searchable_id, 'Status' AS searchable_type, comments.body AS term FROM statuses JOIN comments ON statuses.id = comments.status_id UNION SELECT statuses.id AS searchable_id, 'Status' AS searchable_type, statuses.body AS term FROM statuses UNION SELECT users.id AS searchable_id, 'User' AS searchable_type, users.name AS term FROM users
From here, we add
gin indices to the columns on which we are searching. In
our case similar indices to these made the difference between a 3-5 second
lookup and ~100ms.
CREATE INDEX index_statuses_on_body ON statuses USING gin(to_tsvector('english', body)); CREATE INDEX index_comments_on_body ON comments USING gin(to_tsvector('english', body)); CREATE INDEX index_users_on_name ON users USING gin(to_tsvector('english', name));
Aaron Patterson’s Textacular is the only non-standard dependency we’ll introduce here:
Textacular will manage searching over all text and
We follow Rails’ conventions in our database view, which makes hooking a model
up to it as simple as any table-backed model. The
Search class below
automatically hooks into the
searches view we created.
Luckily, ActiveRecord already presents us with a solution to polymorphic
associations in the form of the
All we have to do is tell
Search about its
searchable relationship, define
results method to perform the search, and extend
Textacular in the
class Search < ActiveRecord::Base extend Textacular belongs_to :searchable, polymorphic: true def results if @query.present? self.class.search(@query).preload(:searchable).map!(&:searchable).uniq else Search.none end end end
The call to
preload is used instead of
include because Rails can’t include
polymorphic associations. Calling
preload still loads the related models in as
few SQL statements as possible, but does not allow for querying on the related
models. This isn’t something we need since we immediately map to
Since we could potentially get a result for a model multiple times, for example
if the same term appeared in a status and a comment, we also call
The interface for Search look like this:
- Rails lacks any support for creating or updating database views. Because the
view must be created by calling execute, Rails is unable to dump the view into
db/schema.rb. Your must make the following change to your application configuration:
# config/application.rb config.active_record.schema_format = :sql
If the view needs to change to include additional searchable content, the
upmethod on the migration has to redefine the view entirely and the
downmethod must redefine the view in its previous form.
Remember that creating indices blocks writes by default, which means that the site needs to be in maintenance mode. Create indices concurrently in Rails to avoid this.
Overall, we’ve enjoyed working with this method of full text search. Because it’s awkward to change the view we’d recommend not approaching the search too iteratively; add the tables you know you’ll want to search up front and save yourself some headache.
For even more depth on full-text search in Postgres including materialized views, ranking of different fields, misspelling toleration, and dealing with accents, check out the Postgres full-text search is Good Enough! post by Rach Belaid or Caleb’s talk on this subject Multi-table Full Text Search in Postgres.