Error ERROR (undefined_table) in a full text search with Ecto and PostgreSQL

Question

I'm trying my implement a very basic full text search in my Phoenix application using Ecto and PostgreSQL. Based on one example and PostgreSQL docs I have in my controller:

def search(conn, %{"q" => para}) do
    search = User |> User.search(para) |> Repo.all
    render(conn, "search.html", search: search)
  end

and in my "model" file (along with the definition of the User fields):

def search(query, search_term) do
    (from u in query,
    where: fragment("to_tsvector(u.name) @@ plainto_tsquery(?)", ^search_term),
    order_by: fragment("ts_rank(to_tsvector(u.name), plainto_tsquery(?)) DESC", ^search_term))
  end

I'm getting this error:

ERROR (undefined_table): missing FROM-clause entry for table "u"

Isn't from u in query, enough (name is one of User fields)?

Answer 1

You can't use u.name like that. u is just a name that exists in Elixir when compiling the query to SQL. It gets renamed while it is compiled. You need to add another ? to fragment and pass u.name for that:

def search(query, search_term) do
  from u in query,
    where: fragment("to_tsvector(?) @@ plainto_tsquery(?)", u.name, ^search_term),
    order_by: fragment("ts_rank(to_tsvector(?), plainto_tsquery(?)) DESC", u.name, ^search_term)
end