phrase search in postgresql - Rails 4

Question

Okay I have implemented a search function in my rails 4 app where users can search other users by name. The search uses jquery foxycomplete. Searches which contain a single word return successfully. Howerver, when I do a phrase search, (e.g john hatfield) postgresql throws and error

 syntax error in tsquery: "john hatfield:*"

This is what I have in my Model

    def self.search(query)
        conditions = <<-EOS
                        to_tsvector('english', name) @@ to_tsquery('english', ?)
                    EOS
        where(conditions, query + ':*')
    end

My controller

@users = User.search(params[:q])

I've searched around Google and I can't seem to find a solution to my problem. Any insights? I want to be in a position to search. Thanks

See [How do you do phrase-based full text search in postgres that takes advantage of the full-text index?](http://stackoverflow.com/q/1489617/238814) — Daniel Vérité, Jan 03 '14 at 20:54

score 0 · Answer 1 · edited Feb 27 '14 at 19:09

For those who may encounter a similar problem. I finally got this working using this article http://www.scottlowe.eu/blog/2011/04/28/postgresql-full-text-search-is-often-good-enough/

As explained all you need is add a tsearch column in your table(In this case articles table)

class AddTsearchToArticle < ActiveRecord::Migration
  def self.up
    execute(<<-'eosql'.strip)
      ALTER TABLE articles ADD COLUMN tsv tsvector;

      CREATE FUNCTION articles_generate_tsvector() RETURNS trigger AS $$
        begin
          new.tsv :=
            setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
            setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'B');
          return new;
        end
      $$ LANGUAGE plpgsql;

      CREATE TRIGGER tsvector_articles_upsert_trigger BEFORE INSERT OR UPDATE
        ON articles
        FOR EACH ROW EXECUTE PROCEDURE articles_generate_tsvector();

      UPDATE articles SET tsv =
        setweight(to_tsvector('pg_catalog.english', coalesce(title,'')), 'A') ||
        setweight(to_tsvector('pg_catalog.english', coalesce(body,'')), 'B');

      CREATE INDEX articles_tsv_idx ON articles USING gin(tsv);
    eosql
  end

  def self.down
    execute(<<-'eosql'.strip)
      DROP INDEX IF EXISTS articles_tsv_idx;
      DROP TRIGGER IF EXISTS tsvector_articles_upsert_trigger ON articles;
      DROP FUNCTION IF EXISTS articles_generate_tsvector();
      ALTER TABLE articles DROP COLUMN tsv;
    eosql
  end
end

In the model you could then simply have

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

  def self.query_limit; 25; end
end

Then in your controller

@articles = Article.plain_tsearch(params[:search])

That did it for me! Hope it helps.

phrase search in postgresql - Rails 4

1 Answers1