How to show excerpts from pg-search multisearch re

Question

I've set up pg_search in my Rails app on Heroku:

@query = 'fast'
PgSearch.multisearch(@query) #=>
[#<PgSearch::Document searchable: ferrari, :content: 'this car is really fast'>,
 #<PgSearch::Document searchable: viper, :content: 'a very fast car'>]

I'd like to display these results with excerpts from content to show where the match occurs. I can call excerpt(content, @query) to get exactly what I want when @query is only one word, but excerpt() only handles exact matches, so if:

@query = 'car fast'
PgSearch.multisearch(@query) #=>
[#<PgSearch::Document searchable: ferrari, :content: 'this car is really fast'>,
 #<PgSearch::Document searchable: viper, :content: 'a very fast car'>]

then excerpt(content, @query) is nil because nowhere in content does the exact phrase 'car fast' appear.

I considered excerpt(content, @query.split(' ').first) to at least show something for multi-word queries, but there are still cases such as this:

@query = 'car?'
@results = PgSearch.multisearch(@query) #=>
[#<PgSearch::Document searchable: ferrari, :content: 'this car is really fast'>,
 #<PgSearch::Document searchable: viper, :content: 'a very fast car'>]
excerpt(@results.first.content, @query) #=> nil

So, how do folks show excerpts from search results when using pg_search?

Answer 1

I'm the author and maintainer of pg_search.

Right now there isn't a built-in way to get excerpts along-side your results in pg_search, but there easily could be if I or someone else has the time to build it in.

PostgreSQL has a function ts_headline that you can call which returns a string excerpt as a column.

It might be possible to call something like this (I haven't tested it yet):

PgSearch.multisearch(@query).select(["ts_headline(pg_search_documents.content, plainto_tsquery(?)) AS excerpt", @query])

Then each of your results should have an excerpt method that returns something like what you want.

By the way, this is something that I eventually want to make automatic in pg_search. I just haven't had the time to delve too deeply into it yet.

Answer 2

If you interpolate the string, you will be subject to sql injection attacks.

Since the .select won't accept a parameterized statement like the .where does (Users.where("id = ?", params[:id])), you will need to sanitize explicitly.

sanitized = ActionController::Base.helpers.sanitize(params[:q])
@results = PgSearch.multisearch(params[:q])
                  .select(["ts_headline(pg_search_documents.content, plainto_tsquery('english', ''' ' || '#{sanitized}' || ' ''' || ':*')) AS excerpt"])

Answer 3

FWIW— Following nertzy's example above, I was able to get this to work with the following:

PgSearch.multisearch(@query).select("ts_headline(pg_search_documents.content, plainto_tsquery('english', ''' ' || unaccent('#{@query}') || ' ''' || ':*')) AS excerpt")

I was having trouble getting plainto_tsquery(?) to work, as it was throwing a syntax error. My solution above was simply the result of doing

PgSearch.multisearch(@query).select(["ts_headline(pg_search_documents.content, plainto_tsquery(?)) AS excerpt", @query]).to_sql

and then plugging in the to_tsquery arguments for the new plainto_tsquery call—something I'm sure is not entirely sound, but seems to work.

Answer 4

There's a much easier way, if you don't feel like digging through SQL - you can make use of built in pg_search gem functionality to display excerpts in a really simple and straightforward way:

In your controller:

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

In your view:

= raw(article.pg_search_highlight)

That should do it.