Thanks a lot for helping me.

I’ve starred GitHub - thiagomajesk/searchy: Full-text search capabilities for Ecto · GitHub and will keep an eye on it. The reason why I’ve done this on my own is because I need to rank results and search in related tables.

So I’ve ended up with this one:

At first I’ve added pg_term extension for incomplete word search:

defmodule MyApp.Repo.Migrations.AddPgTrgmExtension do
  @moduledoc """
  Create postgres pg_trgm extension and indices
  """

  use Ecto.Migration

  def up do
    execute("CREATE EXTENSION pg_trgm")
  end

  def down do
    execute("DROP EXTENSION pg_trgm")
  end
end

Then migrated searchable tables and add tsvector field, trigger and index:

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

  def up do
    alter table("performers") do
      add :tsvector, :tsvector
    end

    create index(:performers, [:tsvector], using: "GIN")

    execute("""
    CREATE OR REPLACE FUNCTION performers_tsvector_trigger()
    RETURNS trigger AS $$
    begin
      new.tsvector := setweight(to_tsvector('russian', coalesce(new.bio, '')), 'A');

      return new;
    end
    $$ LANGUAGE plpgsql;
    """)

    execute("""
    CREATE TRIGGER performers_tsvector_update
    BEFORE INSERT OR UPDATE ON performers
    FOR EACH ROW EXECUTE PROCEDURE performers_tsvector_trigger();
    """)

    alter table("users") do
      add :tsvector, :tsvector
    end

    create index(:users, [:tsvector], using: "GIN")

    execute("""
    CREATE OR REPLACE FUNCTION users_tsvector_trigger()
    RETURNS trigger AS $$
    begin
      new.tsvector := setweight(to_tsvector('russian', coalesce(new.last_name, '')), 'A') ||
      setweight(to_tsvector('russian', coalesce(new.first_name, '')), 'B') ||
      setweight(to_tsvector('russian', coalesce(new.email, '')), 'C') ||
      setweight(to_tsvector('russian', coalesce(new.phone, '')), 'C') ||
      setweight(to_tsvector('russian', coalesce(new.city, '')), 'D');

      return new;
    end
    $$ LANGUAGE plpgsql;
    """)

    execute("""
    CREATE TRIGGER users_tsvector_update
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE PROCEDURE users_tsvector_trigger();
    """)

    alter table("topics") do
      add :tsvector, :tsvector
    end

    create index(:topics, [:tsvector], using: "GIN")

    execute("""
    CREATE OR REPLACE FUNCTION topics_tsvector_trigger()
    RETURNS trigger AS $$
    begin
      new.tsvector := setweight(to_tsvector('russian', coalesce(new.name, '')), 'A');

      return new;
    end
    $$ LANGUAGE plpgsql;
    """)

    execute("""
    CREATE TRIGGER topics_tsvector_update
    BEFORE INSERT OR UPDATE ON topics
    FOR EACH ROW EXECUTE PROCEDURE topics_tsvector_trigger();
    """)
  end

  def down do
    execute("DROP TRIGGER performers_tsvector_update on performers;")
    execute("DROP FUNCTION performers_tsvector_trigger();")

    alter table("performers") do
      remove :tsvector
    end

    drop index("performers", [:tsvector])

    execute("DROP TRIGGER users_tsvector_update on performers;")
    execute("DROP FUNCTION users_tsvector_trigger();")

    alter table("users") do
      remove :tsvector
    end

    drop index("users", [:tsvector])

    execute("DROP TRIGGER topics_tsvector_update on performers;")
    execute("DROP FUNCTION topics_tsvector_trigger();")

    alter table("topics") do
      remove :tsvector
    end

    drop index("topics", [:tsvector])
  end
end

Added tsvector type taken from GitHub - thiagomajesk/searchy: Full-text search capabilities for Ecto · GitHub source:

defmodule MyApp.Ecto.Types.TSVector do
  use Ecto.Type

  def type, do: :tsvector

  def cast(tsvector), do: {:ok, tsvector}

  def load(tsvector), do: {:ok, tsvector}

  def dump(tsvector), do: {:ok, tsvector}

  def embed_as(_), do: :self

  def equal?(term1, term2), do: term1 == term2
end

Added tsvector field to all searchable ecto schemas:

    field :tsvector, Proling.Ecto.Types.TSVector

Added Search.Helpers module for convience:

defmodule MyApp.Search.Helpers do
  defmacro tsquery(tsvector, terms, language \\ "russian") do
    quote do
      fragment(
        "? @@ to_tsquery(?, ?)",
        unquote(tsvector),
        unquote(language),
        unquote(terms)
      )
    end
  end

  defmacro tsrankcd(tsvector, terms, language \\ "russian") do
    quote do
      fragment(
        "ts_rank_cd(?, to_tsquery(?, ?))",
        unquote(tsvector),
        unquote(language),
        unquote(terms)
      )
    end
  end
end

And finally added API for using in context:

defmodule MyApp.Production.Performer.Search do
  import Ecto.Query
  import MyApp.Search.Helpers

  @spec search(Ecto.Query.t(), any()) :: Ecto.Query.t()
  def search(query, search_term) do
    search_term = normalize(search_term)

    from q in query,
      join: p in assoc(q, :profile),
      left_join: t in assoc(q, :topics),
      where:
        tsquery(p.tsvector, ^search_term) or tsquery(q.tsvector, ^search_term) or
          tsquery(t.tsvector, ^search_term),
      order_by: [
        desc: tsrankcd(p.tsvector, ^search_term),
        desc: tsrankcd(q.tsvector, ^search_term),
        desc: tsrankcd(t.tsvector, ^search_term)
      ],
      distinct: [q.id]
  end

  defp normalize(terms, operator \\ "&") do
    terms
    |> String.downcase()
    |> String.trim()
    |> String.replace(~r/\(|\)\[|\]\{|\}/u, "")
    |> String.replace(~r/\s+/u, " #{operator} ")
    |> (&(&1 <> ":*")).()
  end
end

I’m gonna star @thiagomajesk answer with searchy because it`s source code greatly led me to the final destination. Also I would like to mention this awesome answer - Search Bar Feature (Phoenix Searching) - #4 by idi527

Hope this step-by-step example could help someone to get things done.

Ecto does not interpolate any values at all, ever. Rather it uses SQL parameters to avoid SQL injection. If you turn on debug logging you should be able to see the query that Ecto runs. Try running that in PSQL and tweaking the values until you get what you want, then adjust your ecto query accordingly.

I don’t know if I understood exactly what you need but as long as you have a ts_vector field generated for the table you want to search, I guess you are good to go. Our use-case is very simplistic and the lib itself is just a thin wrapper that translates the queries according to the postgres docs. If this is not exactly what you need or it’s incomplete, feel free to open a discussion on the repo so we can talk about it .