Select merge with joins and count

Questions / Help
,
1

I am using select merge to build the query dynamically. But i can’t understand why ecto give me these errors.
this is the query

                            Ecto.Query<from r0 in Room,
                            select: merge(f0, %{"$aggregate": %{"$count": %{^"floor" => count(f0.floor)}}})>

this is the code:

                  from(
                  r in rooms,
                 select_merge: %{"$aggregate": %{"$count": %{^field => count(field(r, ^atom_field))}}}
                 )

and I got this error:

                        struct Room `does not have the key :"$aggregate".

And if i use it in joins:

this is the query:

  from(
    h in Hospital,
    right_join: r in "rooms",
    on: h.id == r.hospital_id,
    where: r.name == ^"room 1" and ^true,
    select: merge(h, %{^:rooms => map(r, [:name, :purpose, :description])})
  )

this is the code:

       from(
      [h, ..., r] in hospital,
      select_merge: %{
        ^String_to_atom(rooms) => map(r, [:fields])
      }
    )

the error is:

     Postgrex expected a binary, got :rooms. Please make sure the value you are passing matches the 
     definition in your table or in your query or convert the value accordingly.

If i change the rooms to string it will throw the same error i am getting in the count query.

May be I am missing something very obvious here.

Any help will be much appreciated

Thanks

2

Have you tried adding virtual key :"$aggregate" to Room struct?

3

No i was hoping the result i get back from the Repo have count in this format.
But I think select merge needs the key to be present in the schema.

4

If you use from x in Schema ecto will implicitly expect you want …, select: x. This will return a list of %Schema{} structs, so each field you select must be part of the struct.

2 Likes
5

so how can i pass the field dynamically in the select merge . If I pass it like this:

“id”

^String.to_atom(id) => x.id

It gives me error that it expects a binary. But if i use the field like this:

“id” => x.id
It gives me error that id is not present in the struct.

It only works if i hard code it like this:

id: x.id

So any work around for dynamic interpolation of the key.

Thanks

6

From what I can tell:

  • Key interpolation (key set at run time) works as long as the key is a string. Even query expressions work to some extent (e.g. (a.id + 4) doesn’t make much sense but it works).
  • atoms seem to only work as keys at compile time
defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.Album

  @keys [:run_time_key]
  @keys_list Keyword.new(@keys, &{&1, Atom.to_string(&1)})

  defp swap_key({to_key, from_key}, map) do
    case Map.pop(map, from_key) do
      {value, rest} ->
        Map.put(rest, to_key, value)

      _ ->
        map
    end
  end

  defp keys_swap(map),
    do: List.foldl(@keys_list, map, &swap_key/2)

  def play do
    album_id = 4

    query =
      from(a in Album,
        where: a.id == ^album_id,
        select: %{}
      )

    key = "run_time_key"
    fields = [:title]

    query =
      from(a in query,
        select_merge: %{^key => map(a, ^fields)}
      )

    query
    |> Repo.all()
    # [%{"run_time_key" => %{title: "Portrait In Jazz"}}]
    |> IO.inspect()
    |> Enum.map(&keys_swap/1)
    # [%{run_time_key: %{title: "Portrait In Jazz"}}]
    |> IO.inspect()

    :ok
  end
end
7

Thanks for your reply.
I figured out the problem.
In the select merge in the last paragraph . It states that you have to supply the map or struct to merge on. If you supply struct it’s value must be present in the key in the merge map. Thats why i am getting these errors.

By supplying an empty map or selecting fields from the struct and then pass them to select merge solved the problem.

1 Like