How to construct TSTZRANGE in rom-sql?

Question

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
)

score 1 · Answer 1 · answered Aug 17 '18 at 08:44

1

Check out the 'Advance Postgres support' section of the docs, http://api.rom-rb.org/rom-sql/ROM/SQL/Postgres/Types looks like there is support TsTzRange range('tstzrange', range_read_type(:tstzrange))

answered Aug 17 '18 at 08:44

Calin

6,661
7
49
80

score 0 · Answer 2 · answered Aug 17 '18 at 19:02

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:

  def available(user_id)
    request_ids = requests.by_user_id(user_id).confirmed.pluck(:id)

    # +- 1 hour around request is not available
    # last argument '()' means "do not include boundaries"
    request_when = "TSTZRANGE(
      lower(requests.when) - INTERVAL '1 HOUR',
      upper(requests.when) + INTERVAL '1 HOUR',
      '()'
    )"

    announcement_when = "TSTZRANGE(lower(announcements.when), upper(announcements.when), '()')"

    where(Sequel.lit(
      "(SELECT COUNT(requests.id)
      FROM requests
      WHERE requests.id IN (:request_ids) AND
            #{request_when} && #{announcement_when} AND
            requests.user_id = :user_id) = 0",
      user_id: user_id, request_ids: request_ids
    ))
  end

How to construct TSTZRANGE in rom-sql?

2 Answers2