时间戳

首页 > 解决方案 > JPA - 确保没有预留与所需预留发生冲突

sql - JPA - 确保没有预留与所需预留发生冲突

问题描述

我正在为一个项目构建一个简单的可出租预订应用程序。如果没有其他与所需预订相冲突的预订，用户可以预订房屋。
我有两个实体类——Rentable 和 Reservation
Reservations 有 @ManyToOne 与可出租的关系和 2 个感兴趣的字段——reserveFrom 和 reserveUntil。
问题如下：
因为我希望提供分页功能，我需要一种方法来返回响应块，因此应用程序级解决方案（即，通过将所有内容加载到内存中进行过滤等）非常昂贵。
用户应该能够提供两个日期（从、到），然后返回大块可用的可出租。

有关如何实现此查询的任何指南？我可以看到以下情况：如果存在属于可出租的其他一些预留 r2 使得
r2.reserveFrom < r1.reserveFrom < r2.reserveUntil OR
r2.reserveFrom < r1.reserveUntil < ，则可出租对于特定预留 r1 不可用r2.reserveUntil 或
r1.reserveFrom < r2.reserveFrom < r2.reserveUntil < r1.reserveUntil

到目前为止我想到的一个基线查询如下（输入：f（从），t（到））

SELECT DISTINCT rentable
FROM Rentable rentable JOIN Reservation reservation
WHERE NOT (f BETWEEN reservation.reserveFrom AND reservation.reserveUntil
OR t BETWEEN reservation.reserveFrom AND reservation.reserveUntil
OR (f > reservation.reserveFrom AND t < reservation.reserveUntil))

这样做的问题是，仅仅因为一行通过了过滤器，并不意味着应该返回可出租的（其他一些行可能会取消它的资格）。
我觉得必须涉及一些 EXISTS 子句。任何帮助表示赞赏。

标签： sqlpostgresqljpajpql

解决方案

您对存在的期望是正确的，更具体地说，不存在。看起来您已经确定了重叠覆盖范围，没有必要，Postgres 已经为此内置了运算符。而不是使用单个日期将表日期和参数日期组合到DATERANGE 中，然后应用OVERLAP运算符 (&&)。结果将是一个确实有重叠的列表。这进入了 NOT EXISTS 谓词。

create or replace 
function available_rentables(from_date_in date, to_date_in date)
  returns setof rentable
  language sql
as $$
with request (parm_range) as 
     ( select  daterange ( from_date_in
                         , to_date_in
                         , '[]'
                         )    
     )   
select *
  from rentable  ren
 where not exists 
       (select null 
          from reservation res
          join request   
            on daterange(res.from_date, res.to_date) && parm_range
       );
$$;

以上为您提供了在指定的 from_date、to_date 范围内可用的可出租的列表。以上假设范围包括两个端点。此外，根据整体使用情况，您可以将 from_date、to_date 列保留存储为单个日期范围。
正如@a_horse_with_no_name 所暗示的那样，排他约束将是数据库级别的良好保险。

推荐阅读