Ecto fragment as a query source?

Questions / Help
,
1

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
2

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
)
5 Likes
3

I have the same issue with @sztosz were i wanted to use TABLESAMPLE system_rows(n) instead of RANDOM() and thank you to @mbuhot on his answer, with a little tweak, as I wanted to get the result as an Ecto query:

"img"
|> with_cte("img", as: fragment("select * from images TABLESAMPLE system_rows(1)"))
|> from(as: ^:img)
|> select([img: i],
	%Images{
    title: i.title,
    description: i.description,
    slug: i.slug,
    tags: i.tags,
	}
)
|> limit(1)
|> Repo.paginate()
2 Likes