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?

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.