Excluding overlapped period in doctrine QueryBuilder

Question

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!

Answer 1

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()))
        ;
    }
}