Why ilike doesn't find a search in a string?

In Elixir Phoenix, using PSQL, I have

where: ilike(movie.title, ^wildcard_search),

. Let’s say I have 10 movies and one of the titles is Elixir Phoenix for example if I search for elixir or phoenix it does give me the title and the movie I want. Ok, but if the title is longer, for example: Los Angeles, Washington, Chicago, Houston, Phoenix, Philadelphia, my search of Phoenix fails - it does not give me this title. Reading from here Ecto.Query.API — Ecto v3.6.2, I understand that it should bring me the string I want by the given search, but no. Why?

Best Regards

Hi, @redpoll

Can you show us what wildcard_search look like?

#eex template
      <%= form_for @conn, Routes.movies_path(@conn, :movies),[method: :get, class: "input-group d-flex justify-content-center"], fn f -> %>
        <div class="search input-group">
          <%= search_input f,  :query, class: "form-control rounded text-center my-2", placeholder: "" %>

        <%= submit "Search", class: "btn btn-outline-primary" %>
      <% end %>

  def index(conn, params) do
    movie = Movies.list_movies(params)
    render(conn, "index.html", movies: movies)
  def list_movies(params = %{"query" => search_term}) do
  def search(query, search_term) do
    wildcard_search = "%#{search_term}"

    from movies in query,
    where: ilike(movies.title, ^wildcard_search),
    or_where: ilike(movies.description, ^wildcard_search)

That’s the full search form

OK, so you only prepended a wildcard (%) to search_term. You should also append one.

wildcard_search = "%#{search_term}%"

Note that this renders all indices useless. I think it’s better to invest some time in full-text search, things like tsvector, tsquery and GIN index type.


Not all. Trigram GIN index may work there. However it is better to use FTS there anyway.


You posted this answer right before I edited my previous answer :grinning: