Skip to content

Instantly share code, notes, and snippets.

@filipechagas
Created May 29, 2014 13:27
Show Gist options
  • Save filipechagas/29f740a7adf8b6cae710 to your computer and use it in GitHub Desktop.
Save filipechagas/29f740a7adf8b6cae710 to your computer and use it in GitHub Desktop.
Postgres - FTS - pg_search - solution to problem with slow rank or weighted search on multiple columns
class AddIndexForFullTextSearch < ActiveRecord::Migration
def up
execute "ALTER TABLE users ADD COLUMN tsv tsvector"
execute <<-QUERY
UPDATE users SET tsv = (to_tsvector('english', coalesce("building"."first_name"::text, '')) ||
to_tsvector('english', coalesce("building"."last_name"::text, '')));
QUERY
execute "CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON users FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', first_name, last_name);"
end
def down
execute "drop trigger tsvectorupdate on users"
execute "alter table users drop column tsv"
end
class User < ActiveRecord::Base
include PgSearch
pg_search_scope :search, against: [:first_name, :last_name],
using: {
tsearch: {
dictionary: "english",
tsvector_column: 'tsv'
}
}
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment