CODEWITHSUNDEEP
postgresqlindexingfull-text-searchelixirecto
Sergio Tapia
Sergio Tapia

Reputation: 9833

Using Ecto for Postgres fulltext search on GIN indexes

I have a simple model:

schema "torrents" do
  field :name, :string
  field :magnet, :string
  field :leechers, :integer
  field :seeders, :integer
  field :source, :string
  field :filesize, :string

  timestamps()
end

And I want to search based on the name. I added the relevant extensions and indexes to my database and table.

def change do
  create table(:torrents) do
    add :name, :string
    add :magnet, :text
    add :leechers, :integer
    add :seeders, :integer
    add :source, :string
    add :filesize, :string

    timestamps()
  end

  execute "CREATE EXTENSION pg_trgm;"
  execute "CREATE INDEX torrents_name_trgm_index ON torrents USING gin (name gin_trgm_ops);"

  create index(:torrents, [:magnet], unique: true)
end

I'm trying to search using the search term, but I always get zero results.

def search(query, search_term) do
  from(u in query,
  where: fragment("? % ?", u.name, ^search_term),
  order_by: fragment("similarity(?, ?) DESC", u.name, ^search_term))
end

SELECT t0."id", t0."name", t0."magnet", t0."leechers", t0."seeders", t0."source", 
t0."filesize", t0."inserted_at", t0."updated_at" FROM "torrents" 
AS t0 WHERE (t0."name" % $1) ORDER BY similarity(t0."name", $2) DESC ["a", "a"]

Is something wrong with my search function?

Upvotes: 0

Views: 2004

Answers (1)

mitchellhenke
mitchellhenke

Reputation: 26

My initial guess is that because you're using the % operator, the minimum limit to match is too high for your queries. This limit defaults to 0.3 (meaning that the strings' trigrams are 30% similar). If this threshold isn't met, no results will be returned.

If that is the issue, this threshold is configurable in a couple of ways. You can either use set_limit (docs here), or set the limit on a per query basis.

The set_limit option can be a bit of a hassle, as it needs to be set per connection every time. Ecto (through db_connection) has an option to set a callback function for after_connect (docs here).

To change the limit per query, you can use the similarity function in the where clause, like this:

def search(query, search_term, limit = 0.3) do
  from(u in query,
  where: fragment("similarity(?, ?) > ?", u.name, ^search_term, ^limit),
  order_by: fragment("similarity(?, ?) DESC", u.name, ^search_term))
end

To start, I would try that with a limit of zero to see if you get any results.

Upvotes: 1

Related Questions