Selecting a structure instead of a field with select_merge

Questions / Help
,
1

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:

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

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})
  end)
end

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

vs

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

2

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.

3

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})
  end
end)

works.

4

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)
end)

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

1 Like
5

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.

6

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

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

8

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

9

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