Wrong group query when trying to build dynamically

tanweerdev

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

3 comments

#ecto #query #ecto-query

0 815 3

2019-04-18 14:01:25 UTC

First 3 of 3 Posts

tanweerdev

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

Post #1

al2o3cr

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.

Post #2