Selecting from a fragment in ecto

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?

You can use with_cte and select from that.

"d"
|> 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"))
2 Likes

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 == ^restaurant.id)
    |> select([pt], %{
      total: fragment("
        case
          when ? = 'TABLE_CLOSED' THEN coalesce(?, 0)
        else
          -1 * coalesce(?, 0)
        end
      ", pt.type, pt.total, 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 }} ->
        totals_query
        |> where([p], fragment("(? at time zone ?)::time BETWEEN ? and ?", p.inserted_at, ^timezone, ^from_time, ^to_time))
      _ -> totals_query
    end

    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("""
        case
        when ? != 0 then (sum(?) / ?)::numeric(10, 2)
        else
          0::numeric(10, 2)
        end
      """, d.count, t.total, d.count)
    })
    |> group_by([d, t], [d.dow, d.count])

    stats = main_query |> Repo.all()

    Logger.info("day average => #{inspect(stats)}")

    {:ok, stats}

  end