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?

Help

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 – Metry

prefix: true only looks for prefixes, so for 12345 I get matches searching for 123, but not for 234 – Help

@Help I am facing the same issue now. did you get any solution to it. please help me if you find any solution – Greenhead

@Greenhead 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 :-/ – Help

@Help ok, thank you for confirming – Greenhead

Answer 1

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

1. Generate the migration

    rails g migration AddPgTrgmExtensionToDB

2. Migration file

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

3. Define your PG search scope as per pg_search docs

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

Elliot