Wrong group query when trying to build dynamically

Questions / Help
, ,
1

I am trying to working on a query builder and the example/content is just imaginary.

query = from en in Data.InfectionRevision
query = from q in query, select: map(q, [:is_complete, :is_active])

query = from(q in query,select_merge: %{"$aggregate": %{"$max": %{^"revision_id" => max(q.revision_id)}}})
query = from(q in query,group_by: q.encounter_id,
 select_merge: %{"$group" => %{^"encounter_id" => q.encounter_id}})

But the query I get is having incorrect key for group, although I have passed correct key while building query. this is what I got

#Ecto.Query<from i0 in Data.InfectionRevision, group_by: [i0.encounter_id],
 select: merge(merge(
   map(i0, [:is_complete, :is_active]), 
   %{"$aggregate": %{"$max": %{^"revision_id" => max(i0.revision_id)}}}), 
   %{"$group" => %{^"revision_id" => i0.encounter_id}})>

%{"$group" => %{^"revision_id" => i0.encounter_id}} should be actually %{"$group" => %{^"encounter_id" => i0.encounter_id}}
What Step or part of the query I am building wrong? How to fix it?

Note:

  • sample project on which you can run/build above queries
  • Please ignore the query content, it might not makes sense because its just an example and should work dynamically as its part of dynamic query builder
  • I need to build query step by step from passed params dynamically
2

If I dont apply aggregate, Query is build all just fine

3

I was able to verify a similar behavior with select_merge locally in a different app, but only in a very specific scenario:

  • pinned parameters as map keys (see below)
  • in select_merge
  • the inspect output, as shown above, is incorrect

BUT turning the resulting query into SQL results in both pinned parameters being correctly passed as bind params.

Further experimentation gave an interesting result: the pinned values shown in inspect for the second select_merge are shifted:

query = from en in Data.InfectionRevision
query = from q in query, select: map(q, [:is_complete, :is_active])

query = from(q in query, select_merge: %{"$aggregate": %{"$max": %{^"revision_id" => max(q.revision_id)}}})
query = from(q in query, group_by: q.encounter_id,
  select_merge: %{
    "$group" => %{
      ^"encounter_id" => q.encounter_id,
      ^"infection_id" => q.infection_id
    }
  }
)

should result in output like (predicted, not tested!):

 %{"$group" => %{^"revision_id" => i0.encounter_id, ^"encounter_id" => q.infection_id}})

This makes a little more sense, as converting the query to SQL would show three bind params that get the values ["revision_id", "encounter_id", "infection_id"].

BUUUUUUT, there’s a bigger question. What SQL are you expecting to result from an expression like select_merge: %{"$group" => %{^"encounter_id" => q.encounter_id}})?

Ecto doesn’t send the map part of the expression to the database, but it DOES include a bind variable reference ($1 etc on Postgresql) in the SELECT list. In your example, the generated SQL would look something like:

SELECT o0.is_complete, o0.is_active, $1, max(o0.revision_id), $2, o0.encounter_id GROUP BY o0.encounter_id

bind vars: ["revision_id", "encounter_id"]

Selecting constant values doesn’t seem particularly useful, especially since Ecto ignores them when constructing the result set.

Regarding the inspect bug, I couldn’t produce similar behavior with a more common use of pins, where: some_field == ^"some value" so it may be specific to the display of select_merge or select clauses.

4

Thanks @al2o3cr. This was bug in ecto which has been fixed in master