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?

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 } }