如何使用Doctrine QueryBuilder排除重叠时段查询可用产品

问题根源 #

你现有的查询逻辑存在两个核心错误：

1. 用INNER JOIN关联预订表，会直接过滤掉没有任何预订记录的产品，但这类产品本身是全时段可用的，符合返回要求
2. 现有WHERE条件仅筛选「产品下存在至少一条和目标时段不重叠的预订」，只要产品有#1/#5类预订，哪怕同时存在重叠的#2/#3/#4/#6类预订，也会被命中返回，完全不符合筛选逻辑

正确逻辑思路 #

我们要筛选的是：不存在任何一条和目标时段重叠的预订的产品
首先明确两个时段重叠的判定条件：预订开始时间 < 目标时段结束时间 AND 预订结束时间 > 目标时段开始时间，所有#2/#3/#4/#6类预订都满足该条件，只要产品存在任意一条满足该条件的预订，就应该被排除。

实现方案 #

推荐用NOT EXISTS子查询实现，逻辑直观且性能更优：

class ProductRepository extends EntityRepository
{
    public function getAvailableProducts(\DateTimeInterface $startAt, \DateTimeInterface $endAt): array
    {
        $qb = $this->createQueryBuilder('p');
        
        // 子查询：查找当前产品下和目标时段重叠的预订
        $subQb = $this->_em->createQueryBuilder()
            ->select('1')
            ->from('Your\Entity\Booking', 'b') // 替换为你自己的Booking实体命名空间
            ->where('b.product = p.id')
            ->andWhere('b.startAt < :endAt')
            ->andWhere('b.endAt > :startAt');
        
        return $qb
            ->where($qb->expr()->not($qb->expr()->exists($subQb->getDQL())))
            ->setParameter('startAt', $startAt)
            ->setParameter('endAt', $endAt)
            ->getQuery()
            ->getResult();
    }
}

如果需要同时加载产品关联的所有预订数据，可以改用LEFT JOIN的方式实现：

class ProductRepository extends EntityRepository
{
    public function getAvailableProducts(\DateTimeInterface $startAt, \DateTimeInterface $endAt): array
    {
        return $this->createQueryBuilder('p')
            ->addSelect('b')
            // 先左连接所有重叠预订
            ->leftJoin('p.bookings', 'b', 'WITH', 'b.startAt < :endAt AND b.endAt > :startAt')
            // 过滤存在重叠预订的产品
            ->where('b.id IS NULL')
            // 再加一次左连接加载产品所有关联预订
            ->leftJoin('p.bookings', 'all_b')
            ->addSelect('all_b')
            ->setParameter('startAt', $startAt)
            ->setParameter('endAt', $endAt)
            ->getQuery()
            ->getResult()
        ;
    }
}

小提示：Doctrine本身支持直接传入DateTimeInterface类型作为时间参数，不需要手动格式化为字符串，框架会自动适配数据库的时间字段格式。

内容的提问来源于stack exchange，提问作者Benjamin