Dynamic fields in Ecto queries

Hi there,

How I can build queries with dynamic fields assignment in Ecto?
I try to do next thing:

import Ecto.Query
field = :id
value = 1

from(o in Order, where: o[field] != ^value)

** (Ecto.Query.CompileError) `o[field]` is not a valid query expression
    (ecto) expanding macro: Ecto.Query.where/3
           iex:6: (file)
    (ecto) expanding macro: Ecto.Query.from/2
           iex:6: (file)

how you can see it doesn’t work, but who cares? keyword syntax allow to do next things:

from(o in Order, where: ^[{field, value}])

and this works fine, but only for simple cases, it doesn’t allow neglect syntax (!=), like, is not, etc.

I don’t give up, and try to apply some metaprogramming magic:

(quote do: unquote(Order) |> where([q], q.unquote(field) == ^unquote(var!(value)))) |> Code.eval_quoted |> elem(0)

And it works perfect, however I need to make join with dynamic association, the next example throw exception and I have no idea how to solve it:

assoc = :transaction

(quote do: unquote(Order |> Ecto.Queryable.to_query) |> join(:inner, [r], l in assoc(r, unquote(var!(assoc)))) |> where([r, l], l.unquote(field) == ^unquote(var!(value)))) 
|> Code.eval_quoted 
|> elem(0)
** (CompileError) nofile: invalid quoted expression: #Ecto.Query<from o in Store.Order>
      (ecto) expanding macro: Ecto.Query.where/3
             nofile:1: (file)
    (elixir) expanding macro: Kernel.|>/2
             nofile:1: (file)

but if I quote module all works fine

how we see there is only one problem that join macros throw error when I try to pass Ecto.Query struct to it. Next snippet works well, but in my app I would like to pass exactly Ecto.Query to join.

assoc = :transaction

(quote do: unquote(Order) |> join(:inner, [r], l in assoc(r, unquote(var!(assoc)))) |> where([r, l], l.unquote(field) == ^unquote(var!(value)))) 
|> Code.eval_quoted 
|> elem(0)

Schemes:

defmodule Order do
  use Ecto.Schema
  schema "orders" do
    has_one :transaction, Transaction
  end
end

defmodule Transaction do
  use Ecto.Schema

  schema "transactions" do
    belongs_to :order, Order
  end
end

Data:
orders: [{id: 1}, {id: 2}]
transactions: [{id: 1, order_id: 1}, {id: 2, order_id: 2}]

2 Likes

To use dynamic fields you can use the field macro.

https://hexdocs.pm/ecto/Ecto.Query.API.html#field/2

thing = :points
from a in Foo, where: field(a, ^thing) > 8
14 Likes

It works perfect! Thank you!

1 Like

Thanks, works fine.

1 Like