Skip to content
Advertisement

Excluding overlapped period in doctrine QueryBuilder

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.

My guess was to create a query like this:

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:

Here, the subquery find conflicting bookings (thanks @nice_dev in comments).

Doctrine repository:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement