Search Bar Feature (Phoenix Searching)

Hi all, am a kind of “learn by doing” type of a person and my current task is I want to implement a searching bar in my index.html.eex where a user can search for a field like location name but i have no idea how to implement that apart from the fact that i will have to describe my query function somewhere maybe in my model.

Any suggestions on best i can tackle this. Thank you all

Hi!

Do you want the search to happen over a channel (websocket) or http request? Is the data that you search through in a database? Is it postgres?

I want it to happen on a http request and yeah i have the data in a database. Yes am using postgres

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

okay let me try this out

Trying to implement postgres full-text search with Ecto, I found this topic.

Instead of the tsv gin index, did not you want to talk about the tsv column itself?

Don’t postgres gin indexes automatically refresh?

:wave:

Can’t find the line containing tsv gin index in my response …

Don’t postgres gin indexes automatically refresh?

All indexes automatically refresh when the indexed data changes, at least that’s what I tend to believe.

1 Like

I am talking about the index created on the tsv column:

 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

Thanks I think the trigger is for updating the tsv column then.

Ah, I think I understand now.

Thanks I think the trigger is to update the tsv column.

Yes, the trigger updates the tsv column, but we don’t actually care about that. What we do care about is the index which also gets updated since we update the column it indexes (namely, tsv). Updating the index via the tsv column is an implementation detail and somewhat of a limitation of full-text search in postgres.

2 Likes

Ok now I get it. ^^