Doctrine查询构建器中如何排除重叠时间段

在Doctrine中，当需要查询某个时间段内可用的产品，并且这些产品可能存在预订记录时，排除已被预订的时间段变得至关重要。核心问题在于如何有效地检测时间段的重叠，并利用QueryBuilder构建相应的SQL查询。

要检测时间段的重叠，需要考虑以下三种情况：

1. 预订的开始时间在给定的时间段内。
2. 预订的结束时间在给定的时间段内。
3. 给定的时间段的开始时间在预订的开始时间和结束时间之间。

满足以上任何一个条件，都意味着预订与给定的时间段存在重叠。

以下是一个使用Doctrine QueryBuilder实现此逻辑的示例：

<?php

namespace App\Repository;

use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use App\Entity\Product;
use DateTimeInterface;

/**
 * @extends ServiceEntityRepository<Product>
 *
 * @method Product|null find($id, $lockMode = null, $lockVersion = null)
 * @method Product|null findOneBy(array $criteria, array $orderBy = null)
 * @method Product[]    findAll()
 * @method Product[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class ProductRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Product::class);
    }

    /**
     * @return Product[] Returns an array of Product objects
     */
    public function getAvailableProducts(\DateTimeInterface $startAt, \DateTimeInterface $endAt): array
    {
        $qb = $this->createQueryBuilder('p');
        $qb->select('p')
           ->leftJoin('p.bookings', 'b')
           ->where(
               $qb->expr()->not(
                   $qb->expr()->exists(
                       $this->createQueryBuilder('sub_p')
                           ->select('1')
                           ->from('App\Entity\Booking', 'sub_b')
                           ->where('sub_b.product = p.id')
                           ->andWhere(
                               $qb->expr()->orX(
                                   $qb->expr()->between('sub_b.startAt', ':startAt', ':endAt'),
                                   $qb->expr()->between('sub_b.endAt', ':startAt', ':endAt'),
                                   $qb->expr()->andX(
                                       $qb->expr()->lte('sub_b.startAt', ':startAt'),
                                       $qb->expr()->gte('sub_b.endAt', ':endAt')
                                   )
                               )
                           )
                   )
               )
           )
           ->setParameter('startAt', $startAt)
           ->setParameter('endAt', $endAt);

        return $qb->getQuery()->getResult();
    }
}

代码解释：

1. createQueryBuilder('p'): 创建一个查询构建器，别名为 'p'，代表 Product 实体。
2. leftJoin('p.bookings', 'b'): 使用左连接将 Product 实体与 Booking 实体关联起来，别名为 'b'。
3. where(...): 添加 WHERE 子句来过滤结果。
4. $qb->expr()->not($qb->expr()->exists(...)): 使用 NOT EXISTS 子查询来查找没有在指定时间段内被预订的产品。
5. $this->createQueryBuilder('sub_p')->select('1')->from('App\Entity\Booking', 'sub_b'): 创建一个子查询构建器，用于检查产品是否在指定时间段内被预订。
6. ->where('sub_b.product = p.id'): 子查询的 WHERE 子句，确保子查询只查找与当前产品相关的预订。
7. ->andWhere($qb->expr()->orX(...)): 子查询的 AND WHERE 子句，使用 OR 表达式来检查预订是否与指定时间段重叠。
8. $qb->expr()->between('sub_b.startAt', ':startAt', ':endAt'): 检查预订的开始时间是否在指定时间段内。
9. $qb->expr()->between('sub_b.endAt', ':startAt', ':endAt'): 检查预订的结束时间是否在指定时间段内。
10. $qb->expr()->andX($qb->expr()->lte('sub_b.startAt', ':startAt'), $qb->expr()->gte('sub_b.endAt', ':endAt')): 检查预订的开始时间是否早于或等于指定时间段的开始时间，并且预订的结束时间是否晚于或等于指定时间段的结束时间。
11. setParameter('startAt', $startAt): 设置参数 startAt 的值。
12. setParameter('endAt', $endAt): 设置参数 endAt 的值。
13. getQuery()->getResult(): 执行查询并返回结果。

注意事项：

• 确保实体类 Product 和 Booking 已经正确定义，并且 Product 实体中存在 bookings 属性，用于关联 Booking 实体。
• startAt 和 endAt 参数应该是 \DateTimeInterface 类型的对象。
• 此方法返回的是 Product 对象的数组，表示在指定时间段内可用的产品。

总结：

通过使用 Doctrine QueryBuilder 和 NOT EXISTS 子查询，可以有效地排除在给定时间段内已被预订的产品。 这种方法能够准确地检测时间段的重叠，并返回符合条件的可用产品。