How to create a rollup query in Ecto where the columns are dynamic?

Hello everyone,

I’m currently struggling with creating a rollup query in Ecto where the columns are dynamic. I have tried to craft my own rollup function using fragment and macro, but it all failed. Basically I would like to apply rollup to entry_details table using different columns combination.

Here’s my current query:

columns = [:activity, :project]


select_columns = Enum.reduce(columns, %{}, fn col, acc ->
  Map.put(acc, col, dynamic([e], field(e, ^col)))
end)

grouping_columns = Enum.reduce(columns, [], fn col, acc ->
  [dynamic([e], field(e, ^col)) || acc]
end)

from(e in "entry_details",
        where:
          e.entry_date >= ^~D[2023-01-01] and e.entry_date <= ^~D[2023-01-31],
        group_by: rollup(^grouping_columns),
        select: %{
          time_spent: sum(e.time_spent),
          value: sum(e.value)
        },
        select_merge: ^select_columns)
|> Repo.all()

And here’s the rollup macro I created:

defmodule Rollup do
  defmacro rollup(columns) do
    quote do
      fragment("ROLLUP ?", unquote(cols_list(columns)))
    end
  end

  defp cols_list(columns) do
    q = List.duplicate("?", Enum.count(columns)) |> Enum.join(",")
    quote do: fragment(unquote("(" <> q <> ")"), unquote_splicing(columns))
  end
end

Unfortunately, this isn’t working for me.

** (Protocol.UndefinedError) protocol Enumerable not implemented for {:^, [line: 19], [{:grouping_columns, [line: 19], nil}]} of type Tuple.

and starting to feel lost with this issue…

I would greatly appreciate any help or suggestions on how to create a dynamic rollup query in Ecto. Thank you!

1 Like

The root issue you’re hitting is that a macro like rollup/1 gets its arguments as AST: rollup’s first argument is literally the syntax tree for ^grouping_columns, not the list of columns that cols_list expects.

You likely want to use dynamic at the outside, instead of for each field, since it can only be used at the root of group_by and select.

1 Like

thanks for the response and your suggestion. It makes more sens now. The below code works:

columns = [:activity, :project]


select_columns = Enum.reduce(columns, %{}, fn col, acc ->
  Map.put(acc, col, dynamic([e], field(e, ^col)))
end)


 q = from(e in "entry_details",
        where:
          e.entry_date >= ^~D[2023-01-01] and e.entry_date <= ^~D[2023-01-31],
        group_by: ^dynamic([e], rollup([field(e, :activity), field(e, :project)])),
        select: %{
          time_spent: sum(e.time_spent),
          value: sum(e.value)
        },
        select_merge: ^select_columns)
|> Repo.all()

However, I have a question on how to make this fields list dynamic because, well, they could be in a variable number, user could choose more fields to use in Rollup like [:project, :activity, :worktype]. Any ideas on how to make that happen? As far as I know the only way to handle this dynamically would be to create this list in similar way to the select_columns map, but that would require using dynamic on individual fields…

Like below:

columns = [:activity, :project]


select_columns = Enum.reduce(columns, %{}, fn col, acc ->
  Map.aput(acc, col, dynamic([e], field(e, ^col)))
end)

field_list = Enum.map(fn col -> dynamic([e], field(e, ^col)) end)


 q = from(e in "entry_details",
        where:
          e.entry_date >= ^~D[2023-01-01] and e.entry_date <= ^~D[2023-01-31],
        group_by: ^dynamic([e], rollup(field_list)),
        select: %{
          time_spent: sum(e.time_spent),
          value: sum(e.value)
        },
        select_merge: ^select_columns)
|> Repo.all()

but then I get similar error as before:

** (Protocol.UndefinedError) protocol Enumerable not implemented for {:field_list, [line: 14], nil} of type Tuple. This protocol is implemented for the following type(s): DBConnection.PrepareStream,

1 Like