Optimize ILIKE Text Search

Questions / Help
, , , ,
1

Hi,

The problem I am encountering is, let’s say my search term is “pasta”, I get the following results:

ID: 3 NAME: a pasta carbonara CREATED_ON: 2022-03-13
ID: 1 NAME: pasta carbonara CREATED_ON: 2022-03-08
ID: 2 NAME: pasta CREATED_ON: 2022-03-13

But the expected results should be in the below order

ID: 2 NAME: pasta CREATED_ON: 2022-03-13
ID: 1 NAME: pasta carbonara CREATED_ON: 2022-03-08
ID: 3 NAME: a pasta carbonara CREATED_ON: 2022-03-13

I want the exact results to come first and then the best similar ones ordered next. Need help with making this work.

Currently I have the following function

def list_recipes(args, prefix_name) do
    sort_params = get_sort_params(args)

    from(f in Food)
    |> order_by(^sort_params)
    |> distinct(true)
    |> put_query_prefix(prefix_name)
    |> match_recipes(args, prefix_name)
    |> Repo.all()
  end

defp get_sort_params(%{sort: %{sort_by: sort_by, sort_order: sort_order}}) do
    [{sort_order, sort_by}, desc_nulls_last: :created_on, asc: :id]
  end

  defp get_sort_params(_args) do
    [desc_nulls_last: :created_on, asc: :id]
  end

defp match_recipes(query, %{filter: filters}, prefix_name) do
    Enum.reduce(filters, query, &text_filter(&1, &2, prefix_name))
  end

  defp match_recipes(query, _filter, _prefix_name), do: query

  defp text_filter({:matching, term}, query, _prefix_name) do
    where(
      query,
      [q],
      ilike(q.name, ^"%#{term}%") or ilike(q.link, ^"%#{term}%")
    )
  end

I have the trigram gin index for recipes table(migration) as follows

def up do
    execute("CREATE EXTENSION IF NOT EXISTS pg_trgm;")
    execute(
      "CREATE INDEX CONCURRENTLY IF NOT EXISTS name_idx_gin ON recipes USING gin (name gin_trgm_ops);"
    )
    execute(
      "CREATE INDEX CONCURRENTLY IF NOT EXISTS link_idx_gin ON recipes USING gin (link gin_trgm_ops);"
    )

    execute("CREATE INDEX CONCURRENTLY IF NOT EXISTS name_idx ON recipes (name);")

    execute("CREATE INDEX CONCURRENTLY IF NOT EXISTS link_idx ON recipes (link);")
  end

  def down do
    execute("DROP INDEX CONCURRENTLY IF EXISTS link_idx;")
    execute("DROP INDEX CONCURRENTLY IF EXISTS name_idx;")
    execute("DROP INDEX CONCURRENTLY IF EXISTS link_idx_gin;")
    execute("DROP INDEX CONCURRENTLY IF EXISTS name_idx_gin;")
  end
2 Likes
2

Well the closest ‘exact’ match will be the shortest, so you could just ORDER BY the result by the name length (or by say exact match else fall back to whatever else you want like created on). Technically they are all matches as it’s the complete word “pasta” though.

2 Likes
3

You could go with full text search instead of using ilike:

7 Likes