排除学说QueryBuilder中的重叠周期

Question

我有Product和Booking实体，我试图选择在给定时期内尚未预订的产品。换句话说，在给定的时间段内选择可用的产品。

为了让自己更容易理解，请看下面的模式。我只想选择有#1或#5预订的产品，因为它是可用的。如果一个产品有一些预订，如#2，#3，#4，#6，不要选择它，因为它是不可用的。

每个|--|代表一个周期，左侧为startAt域，右侧为endAt域。

Past                                              Futur 
|----------------------------------------------------->
Given period
|--------------|
1             .              .
|---------|        .              .
2 .              .
|-----+-|            .
.      3       .
.   |-----|    .
.              . 4
.            |-+-----|
.              .             5
.              .        |---------|
.       6      .
|-----+--------------+-----|
.              .

我的猜想是创建一个这样的查询:

class ProductRepository extends EntityRepository
{
public function getAvailableProducts(DateTimeInterface $startAt, DateTimeInterface $endAt): array
{
return $this->createQueryBuilder('p')
->addSelect('b')
->join('p.bookings', 'b')
->andWhere('b.startAt > :endAt OR b.endAt < :startAt')
->setParameter('startAt', $startAt->format('Y-m-d H:i:s'))
->setParameter('endAt', $endAt->format('Y-m-d H:i:s'))
->getQuery()
->getResult()
;
}
}

但是当andWhere()条件满足模式中的#1或#5时，即使存在#2，#3，#4或#6，数据库(MYSQL 5.6)也会选择该产品

我对这个查询有点困，我觉得我走错了路，所以任何帮助都会很感激!

Answer 1

产品在给定时间段内可能有预订，也可能没有预订。要检测重叠或碰撞，您需要最多检查3个条件:

• 当前行开始日期位于所选日期周期之间
• 当前行结束日期位于所选日期周期之间
• 所选周期的开始日期位于当前行的开始日期和结束日期之间。

原始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);

<<p>

小提琴演示/strong>教义QueryBuilder:

<?php
class ProductRepository extends EntityRepository
{
public function getAvailableProducts(DateTimeInterface $startAt, DateTimeInterface $endAt): array
{
return $this->createQueryBuilder('p')
->addSelect('count(b.*) as cnt')
->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->format('Y-m-d H:i:s'))
->setParameter('endAt', $endAt->format('Y-m-d H:i:s'))
->getQuery()
->getResult()
;
}
}

注意:通过这种方式，您可以检查检索到的记录的计数，并相应地继续前进。

Answer 2

我发现使用NOT EXISTS ()的子查询而不是内部连接是最好的方法:

SELECT *
FROM product p 
WHERE NOT EXISTS (
SELECT * FROM booking b 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 b.product_id = p.id
) IN (1,2);

在这里，子查询找到了冲突的预订(感谢评论中的@nice_dev)。

教义存储库:

class ProductRepository extends EntityRepository
{
private EntityRepository $bookingRepository;
public function __construct(BookingRepository $bookingRepository)
{
$this->bookingRepository = $bookingRepository;
}
public function getAvailableProducts(DateTimeInterface $startAt, DateTimeInterface $endAt): array
{
$bookingQueryBuilder = $this->bookingRepository->createQueryBuilder('b')
->andWhere('b.startAt between :startAt and :endAt OR b.endAt between :startAt and :endAt OR :startAt between b.startAt and b.endAt')
->andWhere('b.product = p')
;
return $this->createQueryBuilder('p')
->andWhere(sprintf('not exists (%s)', $bookingQueryBuilder->getDQL()))
;
}
}