Skip to content

Instantly share code, notes, and snippets.

@bdon
Created June 7, 2012 04:21
Show Gist options
  • Save bdon/2886527 to your computer and use it in GitHub Desktop.
Save bdon/2886527 to your computer and use it in GitHub Desktop.
simple fulltext search with postgres + sequel
if params[:query].present?
# not doing :* prefix matching right now.
# Preprocess the string into what TSvector wants. spaces -> ?
# Then only letters, numbers, underscores and qmarks are left.
query = params[:query].gsub(/\s+/, '?').gsub(/[^\w\?]/, '')
@results = @results.select { [ts_headline('english', :text, to_tsquery('english', query), 'MaxFragments=2').as(headline), id, title, happened_at]}
@results = @results.filter("ts_text @@ to_tsquery('english', ?::text)", query)
end
Sequel.migration do
up do
create_table :transcripts do
primary_key :id
String :text
String :title
DateTime :happened_at
end
run "ALTER TABLE transcripts ADD COLUMN ts_text tsvector;"
run "CREATE INDEX ts_text_idx ON transcripts USING gin(ts_text);"
run "CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON transcripts FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(ts_text, 'pg_catalog.english', text, title, location);"
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment