Selecting from a fragment in ecto

So I’m trying to convert the following sql into ecto

	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 lists.erl:1358: :lists.mapfoldl/3
    (stdlib lists.erl:1359: :lists.mapfoldl/3
    (stdlib 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?

You can use with_cte and select from that.

|> with_cte("d", as: 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')", d),
  count: fragment("to_char(?::date, 'Day')", d) |> count()
|> group_by([_], fragment("dow"))

That worked! with sone modifications. Ive posted the working version for anyone else who runs into this problem.

Basicly this code goes through all days between from and to. it groups by day of the week and the number of times that day occurs. Then it finds the average total sales per day of the week.

The key is to assign the variable to a name. otherwise you only have access to a record and can’t access the actual values.

 def sales_per_day_avg(restaurant, %{from: from, to: to, timezone: timezone} = args, _) do

    totals_query = from(pt in Blinq.Restaurant.PastTransaction)
    |> where([pt], pt.inserted_at >= ^from and pt.inserted_at <= ^to and pt.restaurant_id == ^
    |> select([pt], %{
      total: fragment("
          when ? = 'TABLE_CLOSED' THEN coalesce(?, 0)
          -1 * coalesce(?, 0)
      ", pt.type,, pt.refund_total),
      dow: fragment("to_char((? at time zone ?), 'Day')", pt.inserted_at, ^timezone)

    totals_query = case args do
      %{ time_box: %{ from: from_time, to: to_time }} ->
        |> where([p], fragment("(? at time zone ?)::time BETWEEN ? and ?", p.inserted_at, ^timezone, ^from_time, ^to_time))
      _ -> totals_query

    start_date = DateTime.from_naive!(from, "Etc/UTC")
    end_date = DateTime.from_naive!(to, "Etc/UTC")

    days_query = "d"
    |> with_cte("d", as: fragment("""
      (select generate_series from generate_series(
        (?::timestamp at time zone ?)::timestamp,
        (?::timestamp at time zone ?)::timestamp,
        interval  '1 day'
    """, ^start_date, ^timezone, ^end_date, ^timezone))
    |> select([d], %{
      dow: fragment("to_char(?::date, 'Day')", d.generate_series),
      count: fragment("to_char(?::date, 'Day')", d.generate_series) |> count()
    |> group_by([d], fragment("to_char(?::date, 'Day')", d.generate_series))

    main_query = from(d in subquery(days_query))
    |> join(:left, [d], t in subquery(totals_query), on: t.dow == d.dow)
    |> select([d, t], %{
      day: d.dow,
      average: fragment("""
        when ? != 0 then (sum(?) / ?)::numeric(10, 2)
          0::numeric(10, 2)
      """, d.count,, d.count)
    |> group_by([d, t], [d.dow, d.count])

    stats = main_query |> Repo.all()"day average => #{inspect(stats)}")

    {:ok, stats}