php - Doctrine 2 DQL - Select rows where a many-to-many field is empty?

Question

I have two classes in this example - DeliveryMethod and Country. They have a many-to-many relationship with each other.

What I want to do is select all DeliveryMethods that do not have any Countries mapped to them.

I can do the opposite, that is select all delivery methods that have at least one country -

SELECT m FROM DeliveryMethod m JOIN m.countries

But I can't figure out how to do select where the countries field is empty. In plain SQL I would do the following (deliverymethod_country is the linking table):

SELECT m.* FROM deliverymethods m
LEFT JOIN deliverymethod_country dc ON dc.deliverymethod_id = m.id
WHERE dc.deliverymethod_id IS NULL

However any DQL equivalent of this doesn't work, for example:

SELECT m FROM DeliveryMethod m LEFT JOIN m.countries WHERE m.countries IS NULL

Which gives me this error:

[Syntax Error] line 0, col 75: Error: Expected end of string, got 'm'

Answer 1

Use Doctrine's is empty

It's specifically designed to check for empty associations:

$qb->select('m')->from('DeliveryMethods', 'm')->where('m.countries is empty')

See: Doctrine 2 ORM Documentation: Doctrine Query Language (search for "is empty")