I have Product
and Booking
entity and I’m trying to select products that aren’t already booked in a given period. In other words, to select available products during a given period.
To make myself understandable, see the schema below. I only want to select product that have a booking like #1 or #5 since it is available. If a product has some booking like #2, #3, #4, #6, don’t select it since it is not available.
Each |--|
represents a period, left side the startAt
field and at the right side the endAt
field.
Past Futur
|----------------------------------------------------->
Given period
|--------------|
1 . .
|---------| . .
2 . .
|-----+-| .
. 3 .
. |-----| .
. . 4
. |-+-----|
. . 5
. . |---------|
. 6 .
|-----+--------------+-----|
. .
My guess was to create a query like this:
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()
;
}
}
But when the andWhere()
condition is met with #1 or #5 in the schema, the database (MYSQL 5.6) select this product even if #2, #3, #4 or #6 exist.
I am a little bit stuck with this query, and I feel I am going the wrong way so any help will be appreciated!
Advertisement
Answer
I found out that using a subquery with NOT EXISTS ()
instead of inner join is the best way to do:
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);
Here, the subquery find conflicting bookings (thanks @nice_dev in comments).
Doctrine repository:
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()))
;
}
}