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!