Clipped on 2013-09-04 10:27:38 -0500
sep 2, 2013
Implementing Multi-Table Full Text Search with Postgres in Rails
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 varchar columns.
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 thesearches 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 the resultsmethod to perform the search, and extend Textacular in the model.
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
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 searchable.
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 uniq.
The interface for Search look like this: Search.new(query: ‘books’).results.
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 up method on the migration has to redefine the view entirely and the down method 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.