Use variable to reference named binding in an Ecto Query

Question

I'm trying to build a function that searches for a term in a field of a given table in a query.

For a query like

initial_query = 
  Answer
  |> join(:left, [a], q in assoc(a, :question), as: :question)
  |> join(:left, [a, q], s in assoc(a, :survey), as: :survey)

I would like to be able to search in the tables referenced by :question and :survey.

Now, this code works:

initial_query
|> or_where(
  [question: t], #:question hard coded
  fragment(
    "CAST(? AS varchar) ILIKE ?",
    field(t, ^field),
    ^"%#{search_term}%"
  )
)

However, I want to have a function that takes the named binding as a parameter, but I can't find a way to do it.

My attempts:

defp search_field(initial_query, table, field, search_term) do
  initial_query
  |> or_where(
    [{table, t}],
    fragment(
      "CAST(? AS varchar) ILIKE ?",
      field(t, ^field),
      ^"%#{search_term}%"
    )
  )
end

Gives the error

** (Ecto.Query.CompileError) unbound variable t in query. If you are attempting to interpolate a value, use ^var expanding macro: Ecto.Query.or_where/3

when called like this:

search_field(initial_query, :question, :text, search_text)

and

defp search_field(initial_query, table, field, search_term) do
  initial_query
  |> or_where(
    [{^table, t}],
    fragment(
      "CAST(? AS varchar) ILIKE ?",
      field(t, ^field),
      ^"%#{search_term}%"
    )
  )
end

Gives

** (Ecto.Query.CompileError) binding list should contain only variables or {as, var} tuples, got: {^table, t} expanding macro: Ecto.Query.or_where/3

---

Is there a way to use a variable to reference a named binding in an Ecto Query?

Garment

Have you btw tried to pin t, as suggested in first error response [{table, ^t}]? – Dickinson

@AlekseiMatiushkin Yes, it gives (Ecto.Query.CompileError) unbound variable `t` in query. If you are attempting to interpolate a value, use ^var, which doesn't make a lot of sense to me. – Komara

Answer 1

So the answer to this question seems to be that there isn't a way supported by Ecto to do this. @maartenvanvliet solution works nicely, with the downside of relying on internal implementation.

My solution to this problem was to have the function search_field to always search in the last joined table, using the ... syntax described here:

# Searches for the `search_term` in the `field` in the last joined table in `initial_query`.
defp search_field(initial_query, field, search_term) do
  initial_query
  |> or_where(
    [..., t],
    fragment(
      "CAST(? AS varchar) ILIKE ?",
      field(t, ^field),
      ^"%#{search_term}%"
    )
  )
end

So this function would be used like this:

Answer
|> join(:left, [a], q in assoc(a, :question), as: :question)
|> search_field(:text, search_text)
|> join(:left, [a, q], s in assoc(a, :survey), as: :survey)
|> search_field(:title, search_text)

Which, in my opinion, still reads nicely, with the downside of requiring that we are able to change the initial_query.

Garment

Answer 2

The trick is to retrieve the position of the named binding in the bindings. The named bindings are stored in the %Ecto.Query{aliases: aliases} field.

def named_binding_position(query, binding) do
  Map.get(query.aliases, binding)
end

def search_field(query, table, field, search_term) do
  position = named_binding_position(query, table)
  query
  |> or_where(
    [{t, position}],
    fragment(
      "CAST(? AS varchar) ILIKE ?",
      field(t, ^field),
      ^"%#{search_term}%"
    )
  )
end

We first lookup the position of the named binding in the query.aliases. Then use this position to build the query.

Now, when we call

Answer
|> join(:left, [a], q in assoc(a, :question), as: :question)
|> join(:left, [a, q], s in assoc(a, :survey), as: :survey)
|> search_field(:question, :text, "bogus")

It should yield something like

#Ecto.Query<from a in Answer,
left_join: q in assoc(a, :question), as: :question,
or_where: fragment("CAST(? AS varchar) ILIKE ?", q.text, ^"%bogus%")>

Of note is that the {t, position} tuples in %Query.aliases to refer to the position of the named binding is an internal implementation and not documented. Therefore, could be subject to change. See https://github.com/elixir-ecto/ecto/issues/2832 for more information

Bilberry

Thanks! Relying on internal implementation is not ideal, but this is a clever solution – Komara