Help with an ecto tsrange query where the query parameter needs to be a list of ranges

Questions / Help
1

I am having trouble converting this SQL query to ecto:

select id, d.start_time, d.end_time,
from date_ranges d
where tsrange(d.start_time, d.end_time) && ANY(array[?])

Where the query parameter needs to be a list of ranges:

[tsrange('2025-07-03 04:00:00Z', '2025-07-04 03:59:59Z'),
 tsrange('2025-03-12 04:00:00Z', '2025-03-22 03:59:59Z'),
 ...
]

Here is my failed attempt:

from d in DateRange,
  select: [:id, :start_time, :end_time],
  where: fragment(
    "tsrange(?, ?, '[]') && ANY(ARRAY[?])",
    d.start_time, d.end_time,
    ^Enum.map(events, fn {s, e} -> fragment("tsrange(?, ?)", s, e) end)
  )

which results in an error:

     error: undefined function fragment/3 (expected DateRange to define such a function or for it to be imported, but none are available)
     │
 328 │           ^Enum.map(events, fn {s, e} -> fragment("tsrange(?, ?)", s, e) end)
     │                                          ^^^^^^^^
2

I figured it out:

  from d in DateRange,
    select: [:id, :start_time, :end_time],
    where: fragment(
      "tsrange(?, ?, '[]') && ANY(ARRAY[?::tsrange[]])",
      d.start_time, d.end_time,
      ^(Enum.map(events, fn {s, e} -> %Postgrex.Range{lower: s, upper: e, lower_inclusive: true, upper_inclusive: false} end))
    )

Sorry for noise.

5 Likes
3

based on your original attempt, I think pinning the start and end values in the Enum.map fn may work as well:

from d in DateRange,
  select: [:id, :start_time, :end_time],
  where: fragment(
    "tsrange(?, ?, '[]') && ANY(ARRAY[?])",
    d.start_time, d.end_time,
    ^Enum.map(events, fn {s, e} -> fragment("tsrange(?, ?)", ^s, ^e) end)
  )