ecto fragment as query source

Question

I'm trying to do something like this:

SELECT days.date, SUM(tes.duration) FROM (
  SELECT 
      DATE_FORMAT(
          now() - INTERVAL (@num := @num + 1) DAY,
          '%Y-%m-%d'
      ) date
  FROM time_entries, (SELECT @num:=-1) num
  LIMIT 31
) AS days
LEFT JOIN (
  SELECT te.duration, DATE_FORMAT(te.date, '%Y-%m-%d') date
  FROM time_entries AS te
  WHERE te.account_id = 50150 AND te.deleted_at IS NULL
) AS tes ON tes.date = days.date
GROUP BY days.date
ORDER BY days.date

But this does not seem possible in ecto. I'm attempting this:

from(
  # creates 30 rows for each date 30 days into the past
  days in subquery(
    from(
      num in "time_entries, (SELECT @num := -1)",
      select: %{
        date: fragment("DATE_FORMAT(NOW() - INTERVAL (@num := @num + 1) DAY, '%Y-%m-%d')")
      }
    )
  ),
  # join each date with time entries on this account and sub duration for each day
  left_join: tes in subquery(
    from(
      # using time_entries__undeleted was really slow for some reason… need to look into that
      te in "time_entries",
      select: %{
        duration: te.duration,
        date: fragment("DATE_FORMAT(?, '%Y-%m-%d')", te.date),
        where: te.account_id == ^user.account_id
      },
    )
  ), on: tes.date == days.date,
  group_by: days.date,
  order_by: days.date
)

But i get the error:

(Mariaex.Error) (1146): Table 'development.time_entries, (select @num := -1)' doesn't exist

I need to compose dynamically on top of this query so I would like to not resort to Ecto.Adapters.SQL.query!. Any ideas?

Answer 1

You cannot do this that way, however syntax SELECT … FROM a, b … is rough equivalent to SELECT … FROM a CROSS JOIN b … which is supported by Ecto. So what you need is

from entry in "time_entries",
  cross_join: num in fragment("(SELECT @num = -1)"),
  …