Ecto: Allowing for multiple selects in a query

[jeregrine]

Problem

When building queries that compose you can append where clauses and limits, but you can only have one select clause defined per query. This causes duplication when building queries that need to append additional values that are not defined in the original schema.

This is problem we've experienced when building API's that require complex query results. Below is a simplified example that, while a bit contrived, represents a real life example.

Example

def summary(query, :average) do
  from v in query, 
    select: %{
       total_count: fragment("count(?) as total_count", v.id),
       value: average(v.runtime)
    }
end

def summary(query, :sum) do
  from v in query, 
    select: %{
       total_count: fragment("count(?) as total_count", v.id),
       value: sum(v.runtime)
    }
end

def summary(query, :median) do
  from v in query, 
    select: %{
       total_count: fragment("count(?) as total_count", v.id),
       value: fragment("quantile(?, 0.5) as value", v.time)
    }
end

... continued for multiple summary style stats. Each with a similar structure, but small differences.

Now if we want to rename total_count we'll need to do it in 3 different places.

Proposal

An addition to the Ecto query API called select_merge this will take the existing select map|dict|list and merge it with the one supplied to select_merge overwriting keys in the new map, if no select is currently defined it will use the value as is. Much like the Dict.merge semantics.

Example

def summary_common(query) do
  from v in query,
    select_merge: %{ 
      total_count: fragment("count(?) as total_count", v.id)
    }
end

def summary(query, :average) do
   from(v in query, 
     select: %{
        value:  average(v.runtime)
     })
   |> summary_common()
end
 ...

In my ideal world select would always merge if it encounters multiple select's but I think this is a safer way.

I do not have a strong understanding about how this will change aggregates, groupings, subqueries or joins.

Further Discussion from the Mailing List: https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/elixir-ecto/ymyULSUhPnk/ruDdtk5nBQAJ

[zachdaniel]

@josevalim The map update syntax made me think of this. It could potentially be a good way to implement the basics of multiple select. A map update could represent a merge of the select statements?

[josevalim]

@zachdaniel the map update syntax assumes the key exists which is the opposite of what we want here (and a bit the opposite of merge).

[JamieREvans]

This is especially valuable when composing queries. @josevalim is there a place we can see priorities for these issues, especially this one?

[josevalim]

There is no priority list. The best way to guarantee this is in the next version of Ecto is by contributing it.

[jeregrine]

😍 Omg thank you so much @josevalim ❤️

[BartOtten]

Right on time! Tnx @josevalim

[zachdaniel]

This feature just eliminated a massive amount of conditional complexity in many parts of our code. As always we are eternally grateful @josevalim.