Skip to content

Instantly share code, notes, and snippets.

@scottlowe
Created April 27, 2011 21:00
Show Gist options
  • Save scottlowe/945202 to your computer and use it in GitHub Desktop.
Save scottlowe/945202 to your computer and use it in GitHub Desktop.
Model code for Postgres full text search example
class Article < ActiveRecord::Base
validates :title, :body, :presence => true
# Note that ActiveRecord ARel from() doesn't appear to accommodate "?"
# param placeholder, hence the need for manual parameter sanitization
def self.tsearch_query(search_terms, limit = query_limit)
words = sanitize(search_terms.scan(/\w+/) * "|")
Article.from("articles, to_tsquery('pg_catalog.english', #{words}) as q").
where("tsv @@ q").order("ts_rank_cd(tsv, q) DESC").limit(limit)
end
# Selects search results with plain text title & body columns.
# Select columns are explicitly listed to avoid returning the long redundant tsv strings
def self.plain_tsearch(search_terms, limit = query_limit)
select([:title, :body, :id]).tsearch_query(search_terms, limit)
end
# Select search results with HTML highlighted title & body columns
def self.highlight_tsearch(search_terms, limit = query_limit)
body = "ts_headline(body, q, 'StartSel=<em>, StopSel=</em>, HighlightAll=TRUE') as body"
title = "ts_headline(title, q, 'StartSel=<em>, StopSel=</em>, HighlightAll=TRUE') as title"
Article.select([body, title, :id]).tsearch_query(search_terms, limit)
end
def self.query_limit; 25; end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment