CODEWITHSUNDEEP
ruby-on-railsrails-postgresqlpg-search
csch
csch

Reputation: 4826

Substring search with pg_search

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?

Upvotes: 8

Views: 2229

Answers (1)

vladiim
vladiim

Reputation: 1960

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
  1. Migration file
    class AddPgTrgmExtensionToDb < ActiveRecord::Migration[7.0]
      def change
        enable_extension 'pg_trgm'
      end
    end
  1. Define your PG search scope as per pg_search docs
    pg_search_scope :search_for, against: :sku, using: { trigram: { word_similarity: true } }

Upvotes: 1

Related Questions