使用 Doctrine QueryBuilder 排除重叠时间段的产品

本文旨在解决在使用 Doctrine QueryBuilder 查询产品时，如何排除在给定时间段内已被预订的产品。通过分析时间段重叠的三种可能情况，并提供相应的 SQL 和 Doctrine QueryBuilder 代码示例，帮助开发者准确筛选出指定时间段内的可用产品。本文还强调了使用 COUNT 函数进行记录统计的重要性，以便后续逻辑处理。

在使用 Doctrine ORM 构建应用程序时，经常需要根据时间段来查询数据。一个常见的场景是查询在特定时间段内可用的产品，这意味着需要排除那些在该时间段内已被预订的产品。本文将详细介绍如何使用 Doctrine QueryBuilder 实现这一目标，并避免时间段重叠带来的问题。

理解时间段重叠

要准确地排除重叠时间段，需要考虑以下三种情况：

1. 预订的开始时间在给定的时间段内： 预订的 startAt 位于给定的 startAt 和 endAt 之间。
2. 预订的结束时间在给定的时间段内： 预订的 endAt 位于给定的 startAt 和 endAt 之间。
3. 给定的时间段完全包含在预订的时间段内： 给定的 startAt 位于预订的 startAt 和 endAt 之间。

只有当以上三种情况都不满足时，才能认为产品在给定的时间段内是可用的。

使用原生 SQL 查询

为了更好地理解时间段重叠的判断逻辑，我们首先来看一个使用原生 SQL 的示例：

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 查询统计了 product 表中 id 为 1 或 2 的产品在 2022 年 1 月 5 日到 2022 年 1 月 15 日期间被预订的次数。WHERE 子句中的三个 OR 条件分别对应了上面提到的三种时间段重叠的情况。

使用 Doctrine QueryBuilder 查询

现在，我们将使用 Doctrine QueryBuilder 来实现相同的查询逻辑。以下是一个 ProductRepository 中的方法，用于查询在给定时间段内可用的产品：

<?php  namespace AppRepository;  use DoctrineORMEntityRepository; use DateTimeInterface;  class ProductRepository extends EntityRepository {     public function getAvailableProducts(DateTimeInterface $startAt, DateTimeInterface $endAt): array     {         return $this->createQueryBuilder('p')             ->select('count(b.id)') // 使用 b.id 而不是 b.*             ->join('p.bookings', 'b')             ->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) // 直接传递 DateTimeInterface 对象             ->setParameter('endAt', $endAt)     // 直接传递 DateTimeInterface 对象             ->getQuery()             ->getSingleScalarResult(); // 使用 getSingleScalarResult() 获取单个标量值     } }

代码解释：

• createQueryBuilder(‘p’): 创建一个针对 Product 实体（别名为 p）的查询构建器。
• select(‘count(b.id)’): 选择预订记录的数量。使用 b.id 而不是 b.* 是更清晰和高效的做法。
• join(‘p.bookings’, ‘b’): 将 Product 实体与 Booking 实体通过 bookings 关联起来（假设 Product 实体中有一个名为 bookings 的关联属性）。
• where(‘b.startAt BETWEEN :startAt AND :endAt’): 第一个 WHERE 条件，检查预订的开始时间是否在给定的时间段内。
• orWhere(‘b.endAt BETWEEN :startAt AND :endAt’): 第二个 WHERE 条件，检查预订的结束时间是否在给定的时间段内。
• orWhere(‘:startAt BETWEEN b.startAt AND b.endAt’): 第三个 WHERE 条件，检查给定的开始时间是否在预订的时间段内。
• setParameter(‘startAt’, $startAt) 和 setParameter(‘endAt’, $endAt): 设置查询参数，将 DateTimeInterface 对象直接传递给 setParameter 方法，Doctrine 会自动处理日期格式的转换。
• getQuery(): 创建 Doctrine 查询对象。
• getSingleScalarResult(): 执行查询并返回单个标量值，即预订记录的数量。

注意事项：

• 确保 Product 实体中存在一个名为 bookings 的关联属性，指向 Booking 实体。
• 直接传递 DateTimeInterface 对象给 setParameter 方法，Doctrine 会自动处理日期格式的转换，无需手动格式化。
• 使用 getSingleScalarResult() 方法获取单个标量值，而不是 getResult() 方法，后者会返回一个数组。

使用结果

该方法返回一个整数，表示在给定时间段内与产品关联的预订数量。您可以根据这个数量来判断产品是否可用。例如，如果数量为 0，则表示产品在给定时间段内是可用的；如果数量大于 0，则表示产品已被预订。

$startAt = new DateTime('2024-01-01'); $endAt = new DateTime('2024-01-10');  $bookedCount = $productRepository->getAvailableProducts($startAt, $endAt);  if ($bookedCount == 0) {     echo "产品在 2024 年 1 月 1 日到 2024 年 1 月 10 日期间可用。"; } else {     echo "产品在 2024 年 1 月 1 日到 2024 年 1 月 10 日期间已被预订。"; }

总结

本文介绍了如何使用 Doctrine QueryBuilder 排除重叠时间段的产品。通过分析时间段重叠的三种可能情况，并提供相应的 SQL 和 Doctrine QueryBuilder 代码示例，帮助开发者准确筛选出指定时间段内的可用产品。请记住，理解时间段重叠的逻辑是编写正确查询的关键。通过使用 COUNT 函数，可以方便地统计预订记录的数量，从而做出相应的判断。