Ecto query builder: how to avoid fixing variable name

I’m writing an ecto query builder which can generate queries dynamically from phoenix params maps, like rummage. This requires using the Ecto.Query.dynamic/2 macro, which is actually quite simple and intuitive, but I’m having the following problem:

I’d like the users of my library to be able to write this:

import Ecto.Query, only: [from: 2]
from x in MySchema,
  where: build_query_from_params(MySchema, params),
  where: custom_condition,
  join: custom_join # etc.

To build the query dynamically, I need something like:

def add_filter_to_query(fragment, "greater_than_or_equal_to", field_atom, value) do
    dynamic([x], field(x, ^field_atom) >= ^value and ^fragment)

The problem is that this forces the user to use x as the variable name for the query. I could bypass this by writing the from x in MySchema myself, but then it would be hard for the user to add custom filters because I’d be fixing the variable name, which should be an implementation detail…

Is there any way I can pass a variable name into the dynamic/2 macro so that this stops being a problem?

For example, something like this:

def add_filter_to_query(fragment, "greater_than_or_equal_to", field_atom, value, variable) do
    dynamic([variable], field(variable, ^field_atom) >= ^value and ^fragment)

The above code doesn’t work, of course, but I think it explains what I’m after.

1 Like

Did you actually try that? Because the [x] is not referring to any x in the final ecto query, but it’s referring to the first binding in that query labeling it as x for the use in your dynamic query. So the user of your function does not need to use from x in Schema, but only needs to make sure the first binding in the query is the one the filters of your function should apply to. In Ecto 3 there’s support coming for named bindings, but currently the order of binding is the thing that matters.

query = 
  from a in MySchema,
    join: b in assoc(a, :assoc_a)
# two bindings in order [MySchema, AssocA]

query =
  from [_, b] in query,
    join: c in assoc(b, :assoc_b)
# now there are three bindings [MySchema, AssocA, AssocB]

So as long as you’re using dynamic([whatever], …) the name you chose will always refer to the first binding, no matter how you name it.

1 Like

That’s not exactly my problem (maybe I have explained myself incorrectly). I’d like my users to do this:

query = build_query(MySchema, params)
customized_query = query |> where([u], u.age > 18)

But that only works if the query starts with from u in MySchema. If I could pass the variable name around, I could have the user pass a variable name and use that name in further transformations.

The same thing is true here: [u] refers to the first binding in the query, not the binding you labeled u.

Edit: Just to make that clear. How you label bindings in different parts handling a query does not matter at all. Only the positioning does matter, like [x] for the first binding or [x, y] for the first two bindings or [_, x] for the second binding of the query. Those labels x or y are not variables and until Ecto 3 is released there’s no support for “named bindings”.

You can also use keyword notation or use field. You can store filters for different binding separately like main_filters and joined_table_filters.

1 Like

Here’s a quick example. No matter what you use as name for a binding in each individual macro it still results in the same query.

iex(1)> import Ecto.Query
iex(2)> first = dynamic([x], == 1)

iex(3)> where(Schema, ^first)
#Ecto.Query<from i in Schema, where: == 1>

iex(4)> from a in Schema, where: ^first
#Ecto.Query<from i in Schema, where: == 1>

iex(5)> query = from a in Schema
#Ecto.Query<from i in Schema>
iex(6)> from b in query, where: ^first
#Ecto.Query<from i in Schema, where: == 1>

And an example with multiple bindings, where you can see nomatter what the query itself looks like the dynamic snippet always works on the first binding.

iex(7)> query = from a in SchemaAssoc, join: b in assoc(a, :schema)
#Ecto.Query<from t in SchemaAssoc, join: i in assoc(t, :schema)>
iex(8)> from [c] in query, where: ^first
#Ecto.Query<from t in SchemaAssoc, join: i in assoc(t, :schema), where: == 1>
iex(9)> from [_, d] in query, where: ^first
#Ecto.Query<from t in SchemaAssoc, join: i in assoc(t, :schema), where: == 1>