I can't find how to write this query in rom-sql. Is it possible to add plain sql to where
?
It looks for announcements which do not intersect with requests.
announcements.when
and requests.when
are tstzrange columns in postgres.
SELECT "announcements".* FROM "announcements" WHERE (
(SELECT COUNT(requests.id)
FROM requests
WHERE requests.id IN (1,2,3) AND
(TSTZRANGE(lower(requests.when) - INTERVAL '1 HOUR', upper(requests.when) + INTERVAL '1 HOUR', '()') &&
TSTZRANGE(lower(announcements.when)), upper(announcements.when), '()') AND
requests.user_id = 42
) = 0
)
I found a solution to my problem. Not sure if it's the right way in rom-sql. I'll accept better answer if any appear.
Until that, here's my solution -
Sequel.lit
In my case I defined a method in Announcements relation: