本文旨在解决在使用 Doctrine QueryBuilder 查询产品时，如何排除在给定时间段内已被预订的产品。通过分析时间段重叠的三种可能情况，并提供相应的 SQL 和 Doctrine QueryBuilder 代码示例，帮助开发者准确筛选出指定时间段内的可用产品。本文还强调了使用 COUNT 函数进行记录统计的重要性，以便后续逻辑处理。
在使用 Doctrine ORM 构建应用程序时，经常需要根据时间段来查询数据。一个常见的场景是查询在特定时间段内可用的产品，这意味着需要排除那些在该时间段内已被预订的产品。本文将详细介绍如何使用 Doctrine QueryBuilder 实现这一目标，并避免时间段重叠带来的问题。
理解时间段重叠
要准确地排除重叠时间段，需要考虑以下三种情况：
- 预订的开始时间在给定的时间段内： 预订的 startAt 位于给定的 startAt 和 endAt 之间。
- 预订的结束时间在给定的时间段内： 预订的 endAt 位于给定的 startAt 和 endAt 之间。
- 给定的时间段完全包含在预订的时间段内： 给定的 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 中的方法，用于查询在给定时间段内可用的产品：
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 函数，可以方便地统计预订记录的数量，从而做出相应的判断。
