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...

Shahjahanpur

Answer 1

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.

Ruthie

It does not work when pass sign from variable: Ecto.Query.where(User, [x], custom_where(x, field, value, sign). It shows this error: (Ecto.Query.CompileError) sign(field(x, ^field), ^value) is not a valid query expression Any suggest ? – Shahjahanpur

It obviously does not work that way because what gets passed to macros is an AST. It should be handled differently, probably with an explicit quoting the outcome of the macro and unquoting the variable passed inside the quote block. – Ruthie