How to get entities in a many-to-many relationship that do NOT have a corresponding linked entity with DQL and Doctrine?

Question

I have a standard many-to-many relationship set up. Entity A can have many of Entity B, and vice versa.

I'm trying to get a list of all Entity A that do NOT have any corresponding Entity B. In SQL, I'd run a query like this:

SELECT a.* FROM entity_a a LEFT JOIN a_b r ON r.AID = a.id WHERE r.BID IS NULL

In this query, a_b is the linking table.

I'm trying to write a DQL statement (or use some other method) to get the same result, but the following does not work:

SELECT s FROM VendorMyBundle:EntityA s LEFT JOIN VendorMyOtherBundle:EntityB u WHERE u IS NULL

How can I achieve what I'm trying to do?

Prototherian

Answer 1

First, I have to underline that usually you should JOIN on the property of the entity (i.e. s), e.g. instead of:

SELECT s FROM VendorMyBundle:EntityA s 
  LEFT JOIN VendorMyOtherBundle:EntityB u WHERE u IS NULL

you should have something like:

SELECT s FROM VendorMyBundle:EntityA s 
  LEFT JOIN s.mylistofb u WHERE u IS NULL

where I'm supposing that in entity A you have defined your relationship as:

class A{
    // ...

    /**
     * @ManyToMany(targetEntity="Vendor\MyBundle\Entity\EntityB")
     * @JoinTable(name="as_bs",
     *      joinColumns={@JoinColumn(name="a_id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="b_id", referencedColumnName="id", unique=true)}
     *      )
     **/
    private $mylistofb;

This stated, if the query is not working yet, then try the following:

SELECT s FROM VendorMyBundle:EntityA s
  WHERE SIZE(s.mylistofb) < 1

It is simplest than the previous and also comes from the official docs (i.e. see "phonenumbers example").

Newt

Thanks for that! This solved the problem. I'm still very used to SQL, not DQL, so some of these syntactic differences trip me up. – Prototherian

@Uthr It's the same for me. – Newt