使用 Doctrine QueryBuilder 排除重叠时间段

本文旨在解决在使用 Doctrine QueryBuilder 查询产品时，排除在指定时间段内已被预订的产品。通过分析时间段重叠的三种情况，并提供相应的 SQL 和 Doctrine QueryBuilder 代码示例，帮助开发者准确筛选出可用的产品。重点在于理解时间段重叠的逻辑，并将其转化为可执行的数据库查询。

在开发涉及预订或时间段管理的应用程序时，经常需要查询在特定时间段内可用的资源。当资源已被部分或完全预订时，我们需要排除这些资源。本文将重点介绍如何使用 Doctrine QueryBuilder 来实现这一目标，特别是如何处理时间段重叠的情况。

要确定一个产品在给定时间段内是否可用，我们需要考虑以下三种情况：

1. 预订的开始时间在给定的时间段内。
2. 预订的结束时间在给定的时间段内。
3. 给定的时间段完全包含在预订的时间段内。

只要满足以上任何一种情况，就表示该产品在给定的时间段内不可用。

SQL 查询示例

以下是一个原始 SQL 查询示例，用于查找在 2022-01-05 00:00:00 到 2022-01-15 00:00:00 期间已被预订的产品：

SELECT count(*) as 'booked_count' FROM product p  INNER JOIN booking b ON p.id = b.product_id  WHERE (         (b.startAt between '2022-01-05 00:00:00' and '2022-01-15 00:00:00') OR          (b.endAt between '2022-01-05 00:00:00' and '2022-01-15 00:00:00') OR          ('2022-01-05 00:00:00' between b.startAt and b.endAt)     )  AND p.id IN (1,2);

这个 SQL 查询首先通过 INNER JOIN 将 product 表和 booking 表连接起来。然后，使用 WHERE 子句来筛选出满足时间段重叠条件的预订记录。BETWEEN 运算符用于检查日期是否在给定的范围内。

Doctrine QueryBuilder 示例

以下是使用 Doctrine QueryBuilder 实现相同功能的代码示例：

<?php  use DoctrineORMEntityRepository; use DateTimeInterface;  class ProductRepository extends EntityRepository {     public function getAvailableProducts(DateTimeInterface $startAt, DateTimeInterface $endAt): array     {         return $this->createQueryBuilder('p')             ->select('p') // 选择整个 Product 实体             ->leftJoin('p.bookings', 'b') // 使用 LEFT JOIN，即使产品没有预订也能返回             ->where('b.startAt BETWEEN :startAt AND :endAt')             ->orWhere('b.endAt BETWEEN :startAt AND :endAt')             ->orWhere(':startAt BETWEEN b.startAt AND b.endAt')             ->setParameter('startAt', $startAt)             ->setParameter('endAt', $endAt)             ->groupBy('p.id') // 确保每个产品只返回一次             ->having('COUNT(b.id) = 0') // 筛选出没有重叠预订的产品             ->getQuery()             ->getResult();     } }

代码解释:

1. createQueryBuilder(‘p’): 创建一个查询构建器，别名为 ‘p’，代表 Product 实体。
2. select(‘p’): 选择 Product 实体，确保返回完整的 Product 对象。
3. leftJoin(‘p.bookings’, ‘b’): 使用 LEFT JOIN 将 Product 实体与 Booking 实体关联。使用 LEFT JOIN 可以确保即使 Product 没有相关的 Booking 记录，也能被查询出来。
4. where(‘b.startAt BETWEEN :startAt AND :endAt’): 第一个条件，检查预订的开始时间是否在给定的时间段内。
5. orWhere(‘b.endAt BETWEEN :startAt AND :endAt’): 第二个条件，检查预订的结束时间是否在给定的时间段内。
6. orWhere(‘:startAt BETWEEN b.startAt AND b.endAt’): 第三个条件，检查给定的时间段是否完全包含在预订的时间段内。
7. setParameter(‘startAt’, $startAt): 设置 startAt 参数的值。
8. setParameter(‘endAt’, $endAt): 设置 endAt 参数的值。
9. groupBy(‘p.id’): 按照 Product 的 ID 进行分组，确保每个 Product 只返回一次。
10. having(‘COUNT(b.id) = 0’): 使用 HAVING 子句来筛选出没有重叠预订的产品。如果 COUNT(b.id) 大于 0，则表示该产品在给定的时间段内有重叠的预订，因此会被排除。

注意事项:

• 确保 Product 实体和 Booking 实体之间存在正确的关联关系。
• 在实际应用中，可能需要根据具体的需求调整查询条件。例如，可能需要添加额外的筛选条件，如产品类型、价格范围等。
• 使用 LEFT JOIN 确保即使没有预订记录的产品也能被查询出来，这对于查找完全可用的产品非常重要。
• 使用 groupBy 和 having 子句来确保每个产品只返回一次，并筛选出没有重叠预订的产品。
• 传递 DateTimeInterface 对象作为参数，而不是格式化后的字符串，可以提高代码的可读性和可维护性。

通过以上方法，可以有效地使用 Doctrine QueryBuilder 排除重叠的时间段，从而准确地查询出在给定时间段内可用的产品。这种方法可以应用于各种需要处理时间段重叠的场景，例如预订系统、会议室管理等。