Search Bar Feature (Phoenix Searching)

Phoenix Forum Questions / Help
,
4

So you can add a controller that would accept your search query and call a search function

# in controller
def get(conn, %{"search" => %{"query" => query}}) do
  render(conn, "search_results.html", results: Data.Resource.search(query))
end

And then in the place where you describe your models add the search function, which if you you use tsvectors would be something like

import Ecto.Query

# https://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES
defmacro plainto_tsquery(query) do
  quote do
    fragment("plainto_tsquery('english', ?)", unquote(query))
  end
end

# https://www.postgresql.org/docs/current/static/textsearch-controls.html#TEXTSEARCH-RANKING
defmacro ts_rank_cd(tsv, query) do
  quote do
    fragment("ts_rank_cd(?, ?)", unquote(tsv), unquote(query))
  end
end

@spec search(String.t) :: [Resource.t()]
def search(query) do
  Resource
  |> where([r], fragment("? @@ ?", r.tsv, plainto_tsquery(^query)))
  |> order_by([r], desc: ts_rank_cd(r.tsv, plainto_tsquery(^query)))
  |> Repo.all()
end

For that to work you would probably need to add a tsv column to your table. I do it like that (sloppy)

# somewhere in migrations
  use Ecto.Migration

  def change do
    alter table(:your_table) do
      add(:tsv, :tsvector)
    end
    create index(:your_table, [:tsv], name: :something_something_name_vector, using: "GIN")
  end

and then add a trigger to update the index when the records are updated

# also in some migration
# reindexes to_tsvector(keywords <> name)
  def up do
    execute("""
    CREATE FUNCTION something_something_trigger() RETURNS trigger AS $$
    begin
      new.tsv :=
        to_tsvector('pg_catalog.english', coalesce(new.keywords, ' ') || coalesce(new.name, ' '));

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

    execute("""
    CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
    ON your_table FOR EACH ROW EXECUTE PROCEDURE something_something_trigger();
    """)
  end

  def down do
    execute("drop trigger tsvectorupdate on your_table;")
    execute("drop function something_something_trigger();")
  end

I found https://www.postgresql.org/docs/current/static/textsearch.html to be very helpful when I tried to do this for the first time …

9 Likes