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'
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")
What about this? Assuming $qb
is your query builder instance
$qb->select('m')
->from('DeliveryMethods','m')
->leftJoin('m.countries','c')
->having('COUNT(c.id) = 0')
->groupBy('m.id');
This would give you the DeliveryMethods which is associated with countries and count of the associated countries is 0
There is no need in joins and havings. Simply use SIZE
function:
$qb->select('m')
->from('DeliveryMethods','m')
->where('SIZE(m.countries) = 0');
This will give you all methods without attached countries
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With