我找不到如何在rom-sql中编写这个查询。是否可以将纯sql添加到where
中
它寻找与请求不相交的公告。announcements.when
和requests.when
是postgres中的tstzrange列。
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
)
发布于 2018-08-17 16:44:03
查看文档的“Advance Postgres support”部分,http://api.rom-rb.org/rom-sql/ROM/SQL/Postgres/Types看起来像是有TsTzRange range('tstzrange', range_read_type(:tstzrange))
的支持
发布于 2018-08-18 03:02:02
我找到了解决我的问题的办法。不确定这在rom-sql中是否是正确的方式。如果有更好的答案,我会接受的。
在此之前,这里是我的解决方案-在我的例子中,Sequel.lit
我在公告关系中定义了一个方法:
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
https://stackoverflow.com/questions/51890115
复制相似问题