Dynamic joins in Ecto Queries?

Ecto.Query.dynamic/2 unfortunately doesn’t seem to support dynamically specifying new joins (only the :on part). Is there any way with an Ecto query to add related tables? E.g. if I have a helper function where the user supplies filter parameters, I could pivot between 2 variants of the from, e.g. something like

query = case has_join?(filter_params) do
  false -> from(x in schema, where: ^dynamicall_generate_where_clause(filter_params))
  true -> from(x in schema, join: y in ^other_schema, where: ^dynamicall_generate_where_clause(filter_params))

But I’d essentially have to hard-code the rules for the available joins… and that’s not ideal when the available joins are already defined (in a sense) in the Ecto schemas, e.g. via has_many. Also, it’s not really scalable – I’d have to potentially code up every combination of the available join tables.

There doesn’t seem to be any way to manipulate the options passed to from/2. E.g.

iex> opts = [where: [x: 13]]
iex> query = from MyThing, opts
** (ArgumentError) second argument to `from` must be a compile time keyword list
    (ecto 3.9.2) expanding macro: Ecto.Query.from/2
    iex:10: (file)

Does anyone have insight into this?

You can only interpolate parts of the query at runtime but not everything like from s in Schema, ^everything_else.

You can build up the query based on conditions like this:

from s in Schema
|> maybe_add_join(some_condition)

defp maybe_add_join(query, true), do: from q in query, join: s2 in Schema2
defp maybe_add_join(query, _), do: query

Ah, that’s brilliant, thank you! Is that syntax correct? It looks like from would end up in there twice?

And related… if there are multiple table sources, doesn’t that affect how the dynamic clauses need to look when filters are being generated?

I am currently dynamically generating filter criteria doing something like this where my filter clauses might be something like %{eq: %{col1: "val1", col2: "val2"}} – the dynamic bit handles that beautifully with something like this:

from(c in schema, where: ^filter_where(clauses, true))

# ...

defp filter_where(%{eq: args} = clauses, existing_conditions) do
    |> Map.delete(:eq)
    |> filter_where(
      |> Enum.reduce(existing_conditions, fn {k, v}, conditions ->
        dynamic([tbl], field(tbl, ^k) != ^v and ^conditions)
  # ... other `filter_where` clauses implement other filter operators

  # Finally, like tail recursion, the map eventually becomes empty and we return the accumulated filter clauses
 defp filter_where(filter, existing_conditions) when map_size(filter) == 0,
    do: existing_conditions

But… if there are joins involved, wouldn’t the dynamic bit need additional arguments? E.g.

dynamic([tbl1, tbl2, ...], field(tbl2, ^k) != ^v and ^conditions)
# instead of 
dynamic([tbl], field(tbl, ^k) != ^v and ^conditions)

Does that make sense? I don’t see how I would handle the 1..n arguments to dynamic, or how I’d reference them on the right side.

Again, thanks for any insight!

Yes that’s no problem. See the docs here: Ecto.Query — Ecto v3.9.2

Ah yes I see the issue. I’m not sure off the top of my head but i’ll think on it.

edit: actually you could use named bindings then you don’t need to know the number. Look here: Dynamic queries — Ecto v3.9.2

Name the join like this:

|> join(:inner, [p], assoc(p, :authors), as: :authors)

Reference the join in dynamic by name:

dynamic([authors: a], a.name)

Ah,I missed that section on Dynamic Queries – thanks!

I’m close, but I’m not sure if I can take advantage of that (yet?) because my filter_where is abstracted – it’s one function used to query on any Ecto schema (I’m trying to make something like an ORM). So the problem is that I can’t add new clauses ahead of time (unless I wrote it as a macro?). Can the binding be a variable?

# Instead of:
dynamic([authors: a], ^dynamic and a.name == ^value)

# Something like?
dynamic([{^binding_name, x}], ^dynamic and x.name == ^value)

where you’d know the binding_name based on the filter parameters supplied

I need to try the above once I’m properly back at my computer…

I’m pretty sure that’s not allowed. There’s one other thing I can think of, if you build up your query carefully you can take advantage of this:

from [p, ..., c] in posts_with_comments, select: {p.title, c.body}

What this syntax means is that you can reference the first source and the last source, without knowing how many are in between. So if for instance you did this, you might be ok:

from s in Schema
|> maybe_add_join1(condition1)
|> maybe_add_filter1(condition1)
|> maybe_add_join2(condition2)
|> maybe_add_filter2(condition2)

defp maybe_add_join1(query, true), do: from q in query, join: s2 in Schema2
defp maybe_add_join1(query, _), do: query
defp maybe_add_filter1(query, true), do: from q in query, where: dynamic([s, ..., s2],  ___)
defp maybe_add_filter1(query, _), do: query
defp maybe_add_join2(query, true), do: from q in query, join: s3 in Schema3
defp maybe_add_join2(query, _), do: query
defp maybe_add_filter2(query, true), do: from q in query, where: dynamic([s, ..., s3],  __ )
defp maybe_add_filter2(query, _), do: query

Sorry I simplified the example to make the idea clearer. But the idea would be to adapt this to your situation.

Also, I’m not really familiar with it, but from the little bits I read, the Ash Framework might be doing something similar to you. Using Ecto to build something like an ORM: GitHub - ash-project/ash: A declarative and extensible framework for building Elixir applications.. It might be worth browsing their code and docs.

If you want to rely on schema defintions you can do:
from a in SchemaA, join: b in assoc(a, :relationship).

1 Like

Dynamic binding using the interpolation syntax you posted is supported!

binding_name = :my_assoc

dynamic([{^binding_name, x}], …)

Edit: Here’s some example usage: janus/filter.ex at main · zachallaun/janus · GitHub

1 Like

This is great, thank you! I have been able to update my code so it dynamically can filter on related tables.

The only thing that’s left is dynamically generating the select clause. It looks like there are some limitations with this… the best I can do I think is to generate different from clauses based on the input criteria, e.g.

|> build_from_clause(fields)
|> add_joins(input)
|> where(^filter_where(input))
# ... 

defp build_from_clause(schema, nil) do
    from(c in schema)

defp build_from_clause(schema, fields) do
    from(c in schema,  select: ^fields)

I confess that despite Ecto queries being some of the most fundamental parts of many apps, I find them to be one of the most confusing parts of Elixir. I think it’s because they are macro based and there’s no normal way of tracking where the values come from. It’s just… POOF… and you do select([c], c) or something and you’re left wondering “where did the c come from?”

Macros can certainly make things more difficult to grok, but I think they’ll somewhat “fade away” as you gain a better mental model of Ecto queries. Here’s some info that might hopefully help:

First, Ecto queries are just data structures. This might seem obvious, but it helps to remember. There’s no real magic going on – every time we add a new clause to a query, Ecto’s just updating and returning a new data structure that includes that added information. It’s only when you pass that query to a Repo function that it gets converted into whatever database-speak your db requires (SQL, for instance).

So, somewhere in this data structure that is a query, there’s a field that’s tracking sources – the tables/subqueries/joins/etc. your query is selecting from. Using your example: from(c in schema). Note that c doesn’t actually matter here and is equivalent to from(schema). That’s because the c binding is local to that particular from. But now we have a query and schema is a source, so you can imagine that there might be an internal field in the query:

query = from(schema)
#=> %Ecto.Query{sources: [schema], ...}

So at this point, our query has a single source. Let’s add another clause:

|> where([c], c.attr == "foo")

We need to reference the source somehow, so we’re using the positional binding c. It could have been anything, though, and it doesn’t have to be the same for later clauses:

|> where([c], c.attr == "foo")
|> or_where([d], d.attr == "bar")
|> or_where([e], e.attr == "baz")

You’d obviously never write this, but in the above, c, d, and e all represent the same source – the first one.

Let’s add some joins:

query =
  from(c in query, join: assoc(c, :first_assoc), join: assoc(c, :second_assoc)

We’ve joined in two associations – by using assoc(c, :first_assoc), Ecto will know to find the source of the joins by looking at the associations defined on our first source, whatever schema was. The query data structure might now look like this:

%Ecto.Query{sources: [schema, first_assoc, second_assoc])

At this point, you can probably guess which positional binding would relate to which source if we added more to the query:

from([a, b, c] in query, ...)

So positional bindings are just based on the order that the sources were added. But what if you don’t know how many sources were added, or the order they were added in? Ecto has two ways (that I know of) to handle this. The first is a special bit of syntax for selecting whatever the last source was:

from([..., last] in query, ...)

# you can also bind the original source and the last source
from([first, ..., last] in query, ...)

# or the first two and the last
from([first, second, ..., last] in query, ...)

# etc.

The second is to use named bindings. When we added our joins in, we could have done this instead:

from(c in query,
  join: assoc(c, :first_assoc), as: :first,
  join: assoc(c, :second_assoc), as: :second

When we do this, you can imagine that the query data structure might look more like this:

%Ecto.Query{sources: [schema, first: first_assoc, second: second_assoc]}

And now we can use those names to refer to those sources:

from([first: f] in query, ...)

# can also use `as`
from(query, where: as(:first).attr == "whatever")

So when we use the various Ecto macros to refer to previous sources, there’s no real magic happening – you’re just giving more convenient names to sources using their position or :as name. Hopefully this helps to demystify it a bit!


Thank you @zachallaun, that’s helpful to remember. I should probably dig into the structure that the macros are creating.

The last thing I need to figure out is the dynamic bit for the join’s on

I’ve been able to do a dynamic join with a function like this, and this works for normal joins:

  defp add_join(query, []), do: query

  defp add_join(query, [assn | rest]) do
    from(a in query, join: b in assoc(a, ^assn), as: ^assn)
    |> add_join(rest)

But I hit a snag when I needed to do a fragment conversion on types…

A regular Ecto query would do it something like this:

query = from r in Foo,
              join: f in ^related,
              on: r.yid == fragment("CAST(? AS VARCHAR)", f.id),
              where: r.xid == "xyz",

The schema defines a has_many something like this:

    has_many(:xrelations, Foo,
      foreign_key: :xid,
      references: :id,
      where: [{:xid, "CAST(? AS UUID)"}]

I’m gonna play around with this more and post back when I figure it out (but any tips/suggestions are welcome).

It definitely gets trickier when you start trying to generically handle associations. Look into the schema reflection API. If you know the source schema, you can “follow the association” with something like:

source_schema.__schema__(:association, assn)

This returns one of the association structs that includes all the custom options you used when you set up the relationship in your schema.

Edit: Here’s some code that I’m using to find the source schema of a query.

Thanks! I’ve been able to dynamically join on arbitrary relations… the problem seems to be when the join needs to be custom, e.g. apply a type conversion on one of the keys used to make the join. If I’m reading the resulting queries correctly, that’s NOT what the has_many/3 :where clause does – any statements there do not affect the the on clause, they only add qualifiers, e.g.

SELECT * FROM table1 AS t0 
INNER JOIN table2 AS t1 ON (t1.xid = t0.id) AND    ...has_many/3 where stuff here...
                           \---not here--/

Does anyone know if Ecto supports specifying type-conversions (i.e. fragments) as a condition of the join?

I can pipe some options into my add_join function, e.g.

id1 = :id
id2 = :xid
other_module = Bar

from(a in query,
      join: b in ^other_module,
          [{^binding, tbl1}, {^assn, tbl2}],
          fragment("CAST(? AS VARCHAR)", field(tbl1, ^id1)) == field(tbl2, ^id2)
      as: ^assn

That dynamic usage is the way to do that.

So close… if I need to supply an ad-hoc argument (i.e. a variable) that specifies conversions for the join, fragment() raises an error:

    # A user-supplied conversion
    f1 = "CAST(? AS VARCHAR)"

    from(a in query,
      join: b in ^other_module,
          [{^binding, tbl1}, {^assn, tbl2}],
          fragment(^f1, field(tbl1, ^id1)) == field(tbl2, ^id2)
      as: ^assn
    |> add_join(rest, schema)


** (Ecto.Query.CompileError) to prevent SQL injection attacks, fragment(...) does not allow strings to be interpolated as the first argument via the `^` operator, got: `f1`

That lead me to this post: Ecto not allowing string interpolation in fragments? - #22 by tfwright

I hadn’t known that there was once a fragment_unsafe macro, but I’m wishing that it were still around. I’ve been playing around with Code.eval_quoted but I can’t seem to figure out the syntax to make fragment accept a user-supplied variable to do the cast operation I need.

There’s no way to have user supplied SQL and use it within a fragment. The best you can do is multiple function heads, which apply different hardcoded type conversions based on user input.

yeah, I’m really pushing the river here. I tried using literal() but that quotes the output.

I think it’s enough for me just to be able to “normalize” the join by forcing both sides to be the same data type (VARCHAR). It doesn’t feel elegant, but this is admittedly an uncommon use-case:

on_clause =
      case normalize_join? do
        false ->
          dynamic([{^binding, tbl1}, {^assn, tbl2}], field(tbl1, ^id1) == field(tbl2, ^id2))

        true ->
            [{^binding, tbl1}, {^assn, tbl2}],
            fragment("CAST(? AS VARCHAR)", field(tbl1, ^id1)) ==
              fragment("CAST(? AS VARCHAR)", field(tbl2, ^id2))

from(a in query,
      join: b in ^other_module,
      on: ^on_clause,
      as: ^assn