Full text search

Copy Markdown View Source

An ilike filter matches literal substrings, so searching for "run" also matches "brunch", while searching for "running" does not match "run". It also has no notion of how well a row matches. PostgreSQL full text search matches word stems, so "run" and "running" find each other, "brunch" is unrelated, and the results can be ranked.

In Flop, full text search can be implemented with a custom field, and the rank with an alias field. This recipe is specific to PostgreSQL.

Search column

Store the document in a tsvector column and index it. A generated column keeps it in sync with the source columns, so nothing in your application has to remember to update it.

defmodule MyApp.Repo.Migrations.AddPetSearchColumn do
  use Ecto.Migration

  def up do
    execute """
    ALTER TABLE pets
    ADD COLUMN searchable tsvector
    GENERATED ALWAYS AS (
      to_tsvector(
        'english',
        coalesce(name, '') || ' ' || coalesce(description, '')
      )
    ) STORED
    """

    execute "CREATE INDEX pets_searchable_idx ON pets USING GIN (searchable)"
  end

  def down do
    execute "DROP INDEX pets_searchable_idx"
    execute "ALTER TABLE pets DROP COLUMN searchable"
  end
end

Concatenating with || yields NULL if any operand is NULL, which would leave the whole document empty for a pet without a description. The coalesce calls prevent this.

Add the column to the schema, so that you can refer to it in a query.

schema "pets" do
  field :name, :string
  field :description, :string
  field :searchable, :string, load_in_query: false
end

Ecto has no tsvector type. The column is only ever used in the WHERE clause, and load_in_query: false keeps it out of the select clause.

Filter

The search is a custom field, because @@ is not one of Flop's operators.

defmodule MyApp.Filters do
  import Ecto.Query

  def search(query, %Flop.Filter{value: value}, _opts) do
    where(
      query,
      [p],
      fragment(
        "? @@ websearch_to_tsquery('english', ?)",
        p.searchable,
        ^value
      )
    )
  end
end
@derive {Flop.Schema,
         filterable: [:search],
         sortable: [:name],
         adapter_opts: [
           custom_fields: [
             search: [
               filter: {MyApp.Filters, :search, []},
               ecto_type: :string,
               operators: [:like]
             ]
           ]
         ]}

The function needs no guard for an empty search box. Flop.validate/2 casts a blank string to nil, and Flop skips filters with a nil value, so the function is only called with something to search for.

Use websearch_to_tsquery rather than to_tsquery. It reads what a user types into a search box, including quoted phrases, or and a leading -, and it never raises. to_tsquery expects operator syntax and raises a syntax error on input like pet &. Use plainto_tsquery if you do not want users to have operators at all.

The filter function ignores the operator. We set operators: [:like] to avoid offering operators that are not supported.

Accents

A search for "cafe" does not find "Café", because the two are different lexemes. The unaccent extension removes the diacritics.

CREATE EXTENSION unaccent;

Adding it to the generated column and to the index is where it gets in the way, because both of them fail:

ERROR 42P17 (invalid_object_definition) generation expression is not immutable
ERROR 42P17 (invalid_object_definition) functions in index expression
must be marked IMMUTABLE

A generated column and an index both store the result of the expression, so PostgreSQL only accepts functions it can rely on to return the same result for the same argument forever, which it calls IMMUTABLE. unaccent/1 is one step weaker, STABLE: it looks up the default dictionary at run time, and that dictionary can be changed.

The two-argument form takes the dictionary as an argument instead of looking it up, so a wrapper around it can be declared immutable.

CREATE FUNCTION immutable_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
  AS $$ SELECT unaccent('unaccent', $1) $$;

Use it on both sides. In the generated column:

to_tsvector('simple', immutable_unaccent(coalesce(name, '')))

And on the search term:

fragment(
  "? @@ websearch_to_tsquery('simple', immutable_unaccent(?))",
  p.searchable,
  ^value
)

The 'simple' configuration is deliberate here. The language configurations stem words, which is usually what you want for a description, but they also expect their own language's spelling, so combine them with unaccent only if that suits your data.

Ranking

ts_rank scores a row against the query. Sorting by it needs the score in the select clause under a name. You can use Ecto.Query.API.selected_as/2 and an alias field for this.

@derive {Flop.Schema,
         filterable: [:search],
         sortable: [:name, :rank],
         adapter_opts: [
           alias_fields: [:rank],
           custom_fields: [
             search: [
               filter: {MyApp.Filters, :search, []},
               ecto_type: :string,
               operators: [:like]
             ]
           ]
         ]}

ts_rank takes the search term as an argument, and the term arrives as a filter parameter, so the select clause can only be built once the parameters are validated. That is why we use Flop.validate/2 and Flop.run/3 rather than Flop.validate_and_run/3 here.

def list_pets(params) do
  opts = [for: Pet]

  with {:ok, flop} <- Flop.validate(params, opts) do
    flop.filters
    |> Flop.Filter.get_value(:search)
    |> ranked_query()
    |> Flop.run(flop, opts)
  end
end

defp ranked_query(term) when is_binary(term) do
  from p in Pet,
    select_merge: %{
      rank:
        selected_as(
          fragment(
            "ts_rank(?, websearch_to_tsquery('english', ?))",
            p.searchable,
            ^term
          ),
          :rank
        )
    }
end

defp ranked_query(_) do
  from p in Pet, select_merge: %{rank: selected_as(fragment("0.0"), :rank)}
end

The second clause matters: a client can order by rank without a search term, and PostgreSQL rejects an ORDER BY on a name the select clause does not define.

Ranking has two limits that come from the alias field:

  • An alias field cannot be filterable, since PostgreSQL does not allow a select alias in a WHERE clause.
  • Cursor pagination cannot use an alias field as cursor for the same reason. Flop.validate/2 returns a validation error for that combination, so use offset or page based pagination when you sort by rank.

Complete example

defmodule MyApp.Pet do
  use Ecto.Schema

  @derive {Flop.Schema,
           filterable: [:search],
           sortable: [:name, :rank],
           default_order: %{
             order_by: [:name],
             order_directions: [:asc]
           },
           pagination_types: [:page, :offset],
           adapter_opts: [
             alias_fields: [:rank],
             custom_fields: [
               search: [
                 filter: {MyApp.Filters, :search, []},
                 ecto_type: :string,
                 operators: [:like]
               ]
             ]
           ]}

  schema "pets" do
    field :name, :string
    field :description, :string
    field :searchable, :string, load_in_query: false
    field :rank, :float, virtual: true
  end
end

defmodule MyApp.Filters do
  import Ecto.Query

  def search(query, %Flop.Filter{value: value}, _opts) do
    where(
      query,
      [p],
      fragment(
        "? @@ websearch_to_tsquery('english', ?)",
        p.searchable,
        ^value
      )
    )
  end
end

defmodule MyApp.Pets do
  import Ecto.Query

  alias MyApp.Pet

  def list_pets(params) do
    opts = [for: Pet]

    with {:ok, flop} <- Flop.validate(params, opts) do
      flop.filters
      |> Flop.Filter.get_value(:search)
      |> ranked_query()
      |> Flop.run(flop, opts)
    end
  end

  defp ranked_query(term) when is_binary(term) do
    from p in Pet,
      select_merge: %{
        rank:
          selected_as(
            fragment(
              "ts_rank(?, websearch_to_tsquery('english', ?))",
              p.searchable,
              ^term
            ),
            :rank
          )
      }
  end

  defp ranked_query(_) do
    from p in Pet, select_merge: %{rank: selected_as(fragment("0.0"), :rank)}
  end
end