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

Questions / Help
,
1

Hello,
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

2

Hi, @redpoll

Can you show us what wildcard_search look like?

2 Likes
3 
#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: "" %>
        </div>

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

#controller

  def index(conn, params) do
    movie = Movies.list_movies(params)
    render(conn, "index.html", movies: movies)
  end

#movie.ex
  def list_movies(params = %{"query" => search_term}) do
    Movies
    |>Movies.search(search_term)
    |>Repo.all()
  end

  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)
  end

That’s the full search form

4

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.

4 Likes
5

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

3 Likes
6

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