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'

Glanville

Answer 1

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

Lama

Almost perfect, just needed to change ->join() to ->leftJoin() because otherwise it limits the query to methods that have at least one country and returns an empty result. – Glanville

I am having a smiliar problem, maybe you can help? #14388297 – Nonattendance

@Lama what about one-to-one relations? – Silvie

@parisssss I guess the same should work in One-To-One as well. On second thought, if the left entity is the owning side, you can just use $qb->expr()->isNull() – Lama

@Lama thanks!!it's just worked with $qb->expr()->isNull() :) – Silvie

@Lama brilliant been struggling with this for hours. – Wedlock

This isn’t working on my end. The other solution submitted by @Kinescope works like a charm – Bombshell

using a having clause mean that the database have to retrieve the rows and then filter them this is counterproductive. You should use @Kinescope 's solution. – Melamie

As stated by @Melamie above, both for readability and performance the 'm.countries is empty' should be used. – Roquelaure

Answer 2

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")

Kinescope

Best answer. I am looking for a way to use this within an Expr context, see #41936397 – Nutritious

This should be the top answer. – Hysteric

Just one remark - if you are building the query with join, you must use leftJoin – Consumption

Answer 3

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

Alva

the better answer, leaves out exceptions where you might need another query because of the 'having'. doctrine will figure out the best strategy, it will for example do an subselect if the relation isnt fetch-joined. – Habitation

Answer 4

Can't join NULL values, IIRC. Apologies for the typo on twitter, should have said "can't".

Packing