Dynamic select limitation for group by cube

Questions / Help
1

trying to use a group by cube and grouping columns through ecto fails through dynamic

wanted fields come through a list variable consol = [ :f1, :f2, :f3]
this works:

gby = Enum.map(consol, &dynamic([r], field(r, ^&1)))

Table
|> order_by([r], ^consol)
|> group_by([r], ^gby)
|> select([r], map(r, ^consol))
|> select_merge([r], %{n: count(r.id)})

and gives

select count(id), f1, f2, f3 from table group by f1, f2, f3

now to dynamically insert cube and grouping to get

select count(id), f1, f2, f3 , grouping(f1), grouping(f2), grouping(f3) from table group by cube f1, f2, f3

could not find a solution

the fields names f1, f2 , f3 and field number are not known in advance

2

If you are using PostgreSQL, then you may be interested in my ecto_olap library. It has very sparse implementation as I haven’t touched it in some time, but I may come back to it after some time. It allows you to write something like:

Repo.all from entry in "grouping",
  group_by: cube([entry.foo, entry.bar, entry.baz]),
  order_by: [entry.foo, entry.bar, entry.baz],
  select: %{foo: entry.foo, bar: entry.bar, baz: entry.baz, count: count(entry.foo)}
#=> [%{foo: "a", bar: 1,   baz: "c", count: 1},
#    %{foo: "a", bar: 1,   baz: "d", count: 1},
#    %{foo: "a", bar: 1,   baz: nil, count: 2},
#    %{foo: "a", bar: 2,   baz: "c", count: 1},
#    %{foo: "a", bar: 2,   baz: nil, count: 1},
#    %{foo: "a", bar: nil, baz: "c", count: 2},
#    %{foo: "a", bar: nil, baz: "d", count: 1},
#    %{foo: "a", bar: nil, baz: nil, count: 3},
#    %{foo: "b", bar: 2,   baz: "d", count: 1},
#    %{foo: "b", bar: 2,   baz: nil, count: 1},
#    %{foo: "b", bar: 3,   baz: "c", count: 1},
#    %{foo: "b", bar: 3,   baz: nil, count: 1},
#    %{foo: "b", bar: nil, baz: "c", count: 1},
#    %{foo: "b", bar: nil, baz: "d", count: 1},
#    %{foo: "b", bar: nil, baz: nil, count: 2},
#    %{foo: nil, bar: 1,   baz: "c", count: 1},
#    %{foo: nil, bar: 1,   baz: "d", count: 1},
#    %{foo: nil, bar: 1,   baz: nil, count: 2},
#    %{foo: nil, bar: 2,   baz: "c", count: 1},
#    %{foo: nil, bar: 2,   baz: "d", count: 1},
#    %{foo: nil, bar: 2,   baz: nil, count: 2},
#    %{foo: nil, bar: 3,   baz: "c", count: 1},
#    %{foo: nil, bar: 3,   baz: nil, count: 1},
#    %{foo: nil, bar: nil, baz: "c", count: 3},
#    %{foo: nil, bar: nil, baz: "d", count: 2},
#    %{foo: nil, bar: nil, baz: nil, count: 5}]
1 Like
3

I have looked at it, but it does not apply, you have to manually mention the field names in a list
since it is a macro you cannot pass the name of the variable holding the field names
at least the cube in the package macro does not cover this case

4

I would need to check how to implement such behaviour. I am also open to PRs with such feature.

5

to implement such behaviour, one would probably use the same machinary as is done for the map
macro inside the select([r], map(r, ^consol)) line in my example, but looking at ecto.query source code it seems very technical and very diluted in a huge code base

6

Did you try Ecto’s fragment functionality in order to add the grouping clauses?

7

I tried everything with already existing fragment and dynamic and the like

fragment needs to know the number of ? and you have to pass as many parameters as there are ?, you cannot pass a list of names, so when they are not known in advance no luck