Ecto fragment as a query source?

I’m trying to use fragment in Ecto.Query.from but I’m guessing it’s not possible?

eg:

query =
  from(
    dates in fragment(
      "generate_series((?)::timestamp, (?)::timestamp, '15 minutes')",
      start_datetime,
      end_datetime
    )
  )

so how could I convert the following sql into ecto?

SELECT
	time_slot
FROM
	generate_series('2020-07-13 10:00:00'::timestamp, '2020-07-13 11:00:00'::timestamp, '15 minutes') AS time_slot
EXCEPT
SELECT
	time_slot
FROM
	availabilities

I ran into this recently. The work around I found was to use a CTE:

"time_slots"
|> with_cte("time_slots",
  as:
    fragment("""
    SELECT generate_series(
      '2020-07-13 10:00:00'::timestamp,
      '2020-07-13 11:00:00'::timestamp,
      '15 minutes') as time_slot
    """)
)
|> from(as: :time_slots)
|> select([time_slots: t], t.time_slot)
|> except(^(Availability |> select([a], a.time_slot)))

Which should produce the SQL:

WITH "time_slots" AS (
  SELECT generate_series(
    '2020-07-13 10:00:00'::timestamp, 
    '2020-07-13 11:00:00'::timestamp, 
    '15 minutes') as time_slot
) 
SELECT t0."time_slot" 
FROM "time_slots" AS t0 
EXCEPT (
  SELECT a0."time_slot" 
  FROM "availabilities" AS a0
)
4 Likes