Substring search with pg_search

Question

I'm using pg_search to search for Products:

class Product
  include PgSearch

  pg_search_scope(
    :search_for,
    against: %i(sku),
    associated_against: { translations: [:name, :description] },
    using: { tsearch: { prefix: true } }
  )
end

This works fine so far. But I'd like to add the option to search for partial keywords, so customers can find a product that has the sku "123456789" by typing "45678". This would be easy to do with LIKE, but can find no way to combine that with pg_search, so the results are combined and ranked together.

How can I get pg_search to find products by partial strings?

As far as I know from working with pg_search, the option `prefix: true` you pass should do the work. You can try using `trigram` instead of `tsearch`, as that one searches through triplets of letters instead of whole string — uno_ordinary, Mar 31 '17 at 15:19
`prefix: true` only looks for prefixes, so for `12345` I get matches searching for `123`, but not for `234` — csch, Apr 03 '17 at 06:57
@csch I am facing the same issue now. did you get any solution to it. please help me if you find any solution — anusha, Dec 30 '20 at 12:52
@anusha sorry, this was almost 4 years ago and I don't remember how I ended up solving that issue. I also don't have access to the codebase anymore, so I cannot look it up :-/ — csch, Dec 31 '20 at 12:28

score 0 · Answer 1 · answered Nov 21 '22 at 05:03

PostGres' pg_trgm extension will let you do fuzzy searches inside a string with it's word_similarity attribute set to true.

Generate the migration

    rails g migration AddPgTrgmExtensionToDB

Migration file

    class AddPgTrgmExtensionToDb < ActiveRecord::Migration[7.0]
      def change
        enable_extension 'pg_trgm'
      end
    end

Define your PG search scope as per pg_search docs

    pg_search_scope :search_for, against: :sku, using: { trigram: { word_similarity: true } }

Substring search with pg_search

1 Answers1