sequel-pg-trgm

Sequel plugin for Postgres' pg_trgm.

gem install sequel-pg-trgm

Install the pg_trgm extension in Postgres:

psql
> CREATE EXTENSION pg_trgm;

sequel-pg-trgm creates a dataset method and a helper for creating an index in migrations.

To create the index column for searching, create a new migration like the following:

Sequel.migration do
  up do
    extension :pg_trgm
    add_pg_trgm(:foods, :name)
  end

  down do
    extension :pg_trgm
    drop_pg_trgm(:foods, :name)
  end
end

class Food < Sequel::Model
  plugin :pg_trgm
end

If you have an application that lets a user search for foods, the query to search the name column on the Food model would be:

Food.dataset.text_search(:name, 'Banana Pancakes')

Will only work for Postgres databases. Right now, all results are ordered by their similarity to the query.

Postgres' pg_trgm has a default threshold of 0.3, and will not return results if any results do not match at least that percentage.

If you are trying to get the Food with name 'Banana Pancakes', you may expect to get that back when searching 'ba' or 'ban', but you will not.

Food.dataset.text_search(:name, 'ba').first
# => nil
Food.dataset.text_search(:name, 'ban').first
# => nil
Food.dataset.text_search(:name, 'bana').first
# => <Food @values={:id=>12, :name=>"Banana Pancakes"}>

However, if you set the limit to be lower, say 0.1, you will get results for less accurate searches. It seems to get set per connection, so just calling select set_limit(0.1); once after connecting won't guarantee the value is always going to be set. Calling it in Sequel's after_connect hook usually solves this.

after_connect = proc do |connection|
  begin
    connection.query("select set_limit(0.1);")
  rescue PG::UndefinedFunction
  end
end
Sequel.connect("postgres://localhost/foods", :loggers => [logger], after_connect: after_connect)