Selecting a structure instead of a field with select_merge

I’ve aggregated multiple calls to different tables into a single query with multiple joins where the is a common value between them (ID). So I have something like:

|> from(as: :table_1)
|> join(:left, [t], t2 in Table2, on: ==, as: :table_2)
|> where([t], == ^id)
|> select([table_1: table_1, ..., table_9: table_9], %{table_1: table_1, ... table_9: table_9})

This works fine, it will have a different structure per field and request all the information I need. Since this can grow in the future, and I decided to request more or fewer tables depending on the use case, I want to make this all dynamically.

I have made this almost work except for the select function, which I cannot find a way to make the same behaviour with select_merge. I’ve changed that to the following structure:

defp apply_table_select(query, tables) do 
  query = select(query, [i], %{table_1: i})

  Enum.reduce(tables, query, fn table, query -> 
    select_merge(query, [{^table, m}], %{^table => m})

But this doesn’t work. I need to add ^ because otherwise (and makes sense since I’m referring to a variable), I have a compile-time error.

** (DBConnection.EncodeError) Postgrex expected a binary, got :table_2. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

The issue appears to be related how I added the field to select_merge:

#Ecto.Query<from i0 ..., select: %{table_1: i0, table_2: t1}>


#Ecto.Query<from i0 ..., select: merge(%{table_1: i0}, %{^:table_2 => t1})>

Not sure if this is a limitation from Ecto or I’m doing something wrong.

EDIT: Just found this topic, basically has the same issue.

Ecto 3.9 added ways to use dynamic in combination with select and select_merge. The docs should help you out on how to apply that.

I don’t think they work for this use case. All the examples on select_merge / dynamic normally specified specific fields, not structures.

Probably not the best solution, but ended up creating a macro to pattern-match all the tables values, since

[:table_1, ...]
|> Enum.each( fn table ->
  def select_merge_table(query, table) when table == unquote(table) do
    select_merge(query, [{^table => m}], %{unquote(table) => m})


Coincidentally, I ran into this same thing yesterday. I ended up solving the overall problem I was facing a different way, but I believe this may work for you:

Enum.reduce(tables, query, fn table, query ->
  to_merge = %{table => dynamic([{^table, m}], m)}
  select_merge(query, ^to_merge)

I can’t test this now but please report back if it works! I’m curious.

1 Like

It doesn’t work. It gives a weird error, probably because is was expecting a field.

** (ArgumentError) expected a list of fields in `select/2` inside `select`, got: `%{table_1: %Inspect.Error{message: "got Ecto.QueryError with message \"unknown bind name `:table_1` in query:\\n\\nfrom q0 in query\\n\" while inspecting %{__struct__: Ecto.Query.DynamicExpr, binding: [{{:^, [line: 652], [{:tables, [line: 652], nil}]}, {:m, [line: 652], nil}}], file: \"/redacted\", fun: #Function<13.96637024/1 in redacted>, line: 652}"}}`

Maybe this could be a feature to be added to ecto, not sure if is something that makes sense to add.

The error makes is seem the issue is in your code and less in how ecto works.

got Ecto.QueryError with message \"unknown bind name `:table_1` in query:

I think @LostKobrakai is correct, the dynamic will require a query that has a :table_1 named binding in order to expand properly.

Yap, looks like it. I will keep the macro since is the only way to get this to work properly.

Finally got time to look into this again, I was still using Ecto 3.8 :triumph: after updating the dependency that worked as a charm.

1 Like