Error interpolating a variable as the first argument of an Ecto fragment

Questions / Help
1

I’m trying to do this:

def search(query, search_term, locale) do
    language =
      case locale do
        "pt" ->
          "Portuguese"
        "es" ->
          "Spanish"
        _ ->
          "English"
      end
    fragmentWhere = "(to_tsvector('#{language}', unaccent(?)) || 
                      to_tsvector(coalesce('#{language}', unaccent(?)))) @@ plainto_tsquery('#{language}', unaccent(?))"
    fragmentOrderBy = "ts_rank((to_tsvector('#{language}', ?) ||to_tsvector(coalesce('#{language}', ?))), 
                      plainto_tsquery('#{language}', ?)) DESC"
    (from u in query,
    where: fragment(^fragmentWhere, u.name, u.name_label, ^search_term),
    order_by: fragment(^fragmentOrderBy, u.name, u.name_label, ^search_term))
    |> Repo.all()
  end

cto.Query.CompileError) fragment(...) expects the first argument to be a string for SQL fragments, a keyword list, or an interpolated value, got: '^fragmentOrderBy'

2

Fragments have to be compile-time strings, it is to prevent sql injection attacks.

Your example can easily be done as something like:



def search(query, search_term, locale) do
    language =
      case locale do
        "pt" ->
          "Portuguese"
        "es" ->
          "Spanish"
        _ ->
          "English"
      end
    (from u in query,
    where: fragment("(to_tsvector(?, unaccent(?)) || 
                      to_tsvector(coalesce(?, unaccent(?)))) @@ plainto_tsquery('#{language}', unaccent(?))", ^language, u.name, ^language, u.name_label, ^search_term),
    order_by: fragment("ts_rank((to_tsvector(?, ?) ||to_tsvector(coalesce(?, ?))), 
                      plainto_tsquery(?, ?)) DESC", &language, u.name, language, u.name_label, ^language, ^search_term))
    |> Repo.all()
  end

Or something like that. :slight_smile:

It is also convenient to hide them behind def/defmacro’s for ease of use too.

1 Like
3

Thank you.
Although making a lot of sense now I was trying all other alternatives but this one…:stuck_out_tongue:
…and couldn’t find anything that led me to this, so double thanks!

1 Like
4

Sorry to bother again, but now when calling this function in my controller I get another error that I can’t find any information about:

Postgrex.Protocol (#PID<0.288.0>) disconnected: ** (RuntimeError) typeregconfigcan not be handled by the types module Ecto.Adapters.Postgres.TypeModule

def search(conn, %{"q" => param}) do
    locale = conn |> get_session(:locale)
    searchEmpresas = Skeleton.Customers.Empresa |> Skeleton.Customers.search(param, locale)
end

Where param and locale are strings such as "magna" and "pt"

5

Ooo, you seem to have a type defined/added in your PostgreSQL server called regconfig, and it is being returned and the postgresql driver does not know how to handle it, you may need to define a converter for it yourself then (I’ve not actually used ts_vectors since I’ve been using elixir yet but that is something that should really be supported somewhere as a converter somewhere…).

I’m unpacking boxes currently (I just moved) or I’d search for you, but maybe check hex.pm and see if anyone’s made some postgresql ts_vector / full text converters for ecto yet?

It would not be hard to make your own though, you can start just by building a converter for it that returns it as a binary then figure out how to parse that and add it to it, but I’d say look for an existing one first.

6

Thank you.
This is already very helpful to find my way :slight_smile:

1 Like