Create Ecto query with dynamic operators

Question

I know how I can use dynamic fields and values into Ecto query, for example:

field = :age
value = 20
Ecto.Query.where(App.User, [x], field(x, ^field) < ^value)

But, it is possible to define the operator (in this example <) dynamically? and how?

I have tried it with fragment and interpolated string:

operator = ">"
Ecto.Query.where(App.User, [x], fragment("? #{operator} ?", field(x, ^field), ^value))

but it raises the exception: Ecto.Query.CompileError) to prevent SQL injection attacks, fragment(...) does not allow strings...

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2019-09-07T06:40:07

You can, with a bit of metaprogramming. ecto supports custom macros.

defmacrop custom_where(t, f, v, sign) do
  {sign, [context: Elixir, import: Kernel],
   [
     {:field, [], [t, {:^, [], [f]}]},
     {:^, [], [v]}
   ]}
end

And use it like

field = :age
value = 20

Ecto.Query.where(User, [x],
  custom_where(x, field, value, :<))
#⇒ #Ecto.Query<from u0 in User, where: u0.age < ^20>

The trick here is that we fool ecto injecting an AST inplace, so it does not perform checks.

Create Ecto query with dynamic operators

1 Answers