ruby-on-railspostgresqlfull-text-searchpg-search

pg_search gem and fulltext search rank setting


Im using the gem pg_search to perform fulltext search on my postgres db. I've a scope like this:

pg_search_scope :fulltext, against: [:firstname, :lastname, :middlename],
                using: {
                  tsearch: { prefix: true },
                    trigram: {
                      :threshold => 0.2
                    }
                  }

When i search with the string gro I would like that the records that match the exactly word gro have rank higher than records that match words like group (group contain gro).

Any idea, if there are some settings to do this?


Solution

  • I had a very similar situation in Django and I solved summing up the trigram similarity and tsearch rank.

    Similarity with word with exact match is higher than a partial match, as you can check on PostgresSQL:

    SELECT similarity('gro', 'gro') AS exact, similarity('gro', 'group') AS partial;
     exact | partial  
    -------+----------
         1 | 0.428571
    

    I'm not a ruby expert but I've read the pg_search documentation and I think the solution for your question should by similar to this:

    pg_search_scope :fulltext,
                    against: [:firstname, :lastname, :middlename],
                    using: {
                      tsearch: { prefix: true },
                      trigram: {
                          :threshold => 0.2
                      }
                    },
                    :ranked_by => ":tsearch + :trigram"