So I’m trying to convert the following sql into ecto
SELECT
to_char(t::date, 'Day') as dow,
count(to_char(t::date, 'Day')) as count
FROM generate_series(timestamp '2004-03-07'
, timestamp '2004-08-16'
, interval '1 day') as t
group by dow
Logically, this should map to the following ecto code
days_query = from(d in fragment("generate_series((? at time zone ?)::timestamp, (? at time zone ?)::timestamp, interval '1 day')", from, timezone, to, timezone))
|> select([d], %{
dow: fragment("to_char(?::date, 'Day') as dow", d),
count: fragment("to_char(?::date, 'Day') as count", d) |> count()
})
|> group_by([d], fragment("dow"))
unfortunately I get the error
== Compilation error in file lib/blinq_api_server_web/resolvers/website/dashboard_resolver.ex ==
** (CompileError) lib/blinq_api_server_web/resolvers/website/dashboard_resolver.ex:830: cannot use ^from outside of match clauses
(stdlib 3.14.2.1) lists.erl:1358: :lists.mapfoldl/3
(stdlib 3.14.2.1) lists.erl:1359: :lists.mapfoldl/3
(stdlib 3.14.2.1) lists.erl:1358: :lists.mapfoldl/3
(ecto 3.5.8) expanding macro: Ecto.Query.group_by/3
lib/blinq_api_server_web/resolvers/website/dashboard_resolver.ex:835: BlinqWeb.Website.DashboardResolver.sales_per_day_avg/3
(elixir 1.11.4) expanding macro: Kernel.|>/2
lib/blinq_api_server_web/resolvers/website/dashboard_resolver.ex:835: BlinqWeb.Website.DashboardResolver.sales_per_day_avg/3
I’m guessing Ecto doesn’t like when you call a fragment inside from. Unfortunately, this is exactly what my use case requires.
Is there another way to do this?