pg_search_scope - some columns ignore search feature

[sotek222]

Hi there,

I am currently using the pg_search_scope method to construct a search against 3 columns in my primary model (Chemical) and 2 columns in an associated model (Component) a many-to-many with a join.

I am also using a combination of the trigram feature (enabled through a migration) and the tsearch feature.

The issue I have discovered is that after adding both features and configuring them certain columns in the primary model ignore the trigram feature. I may be misunderstanding the documentation, but I tried my best to follow it as closely as possible. Any help would be appreciated.

---

Environment -

• Ruby - 2.7.2
  • Rails - 6.0.3
  • PSQL - 12

Primary model (Chemical)

class Chemical < ApplicationRecord
  include PgSearch::Model

  has_many :chemical_components, dependent: :destroy
  has_many :components, through: :chemical_components

  pg_search_scope(
    :search_for, {
      against: { chem_code: 'A', sample_identifier: 'B', name: 'C' },
      associated_against: {
        incis: { region_a_name: 'D', region_b_name: 'D' }
      },
      using: {
        trigram: { threshold: 0.1 },
        tsearch: { prefix: true, any_word: true, normalization: 2, tsvector_column: 'searchable' }
      },
      ranked_by: ':trigram'
    }
  )
end

Example of weird results -

chemical1 = Chemical.create(chem_code: 'RM123', sample_identifier: 'AX123', name: 'Aloe Serum')
chemical2 = Chemical.create(chem_code: 'RMAG03-B', sample_identifier: 'AX234', name: 'Glycerin Extract' )

example 1 - 

Chemical.search_for('serum') # here I would expect the query to match against 'Aloe Serum' (from chemical1)
expectation =>  [chemical1]
reality => []

example 2 - 

Chemical.search_for('G03-B') # here my expectation is that it will use trigram to match against the chem_code
expectation =>  [chemical2]
reality => []

Can anyone tell me if there is something glaringly wrong with my setup or what i'm doing that I may be missing?

Thanks and really appreciate all the hard work you've put into this gem so far!

[nertzy]

When you use tsvector_column, that column is used for the tsearch search feature and the individual columns ( chem_code, etc.) are not actually used.

You may want to ensure that your searchable tsvector column includes the name.

[sotek222]

Thank you for the super prompt reply!

Ah interesting, so does that mean that any columns that are part of a tsvector_column will ignore other features, such as trigram?

also just to give more context here is the migration that adds the ts_vector_column searchable:

  # frozen_string_literal: true

class AddSearchColumnToChemicals < ActiveRecord::Migration[6.0]
  disable_ddl_transaction!

  def up
    execute <<-SQL
      ALTER TABLE chemicals
      ADD COLUMN searchable tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce('chem_code', '')), 'A') ||
        setweight(to_tsvector('english', coalesce('sample_identifier', '')), 'B') ||
        setweight(to_tsvector('english', coalesce('name','')), 'C')
      ) STORED;
    SQL

    add_index :chemicals, :searchable, using: :gin, algorithm: :concurrently
  end

  def down
    remove_index :chemicals, :searchable
    remove_column :chemicals, :searchable
  end
end

Thanks again!