Doctrine2 QueryBuilder: how to filter objects having a zero score for OneToMany

Question

In my Dymfony2 / Doctrine2 application, I have oneToMany relationship between an object and its children.

I want to select all objects that have no children.

I am stuck with various errors: SingleValuedAssociationField is expected, Cannot add Having sth for a variable with no result, etc.

$queryBuilder = $this
    ->createQueryBuilder('object')
    ->leftJoin('object.children', 'children')
    ->andWhere('children IS NULL')
    // tested with a parameter, with addselect COUNT(children) and 0 condition, etc.
    ->getQuery()
    ->getResult();

How can i solve this?

Answer 1

There is a selector called SIZE()that should do the trick. Read more here.

Try something like this:

$this
    ->createQueryBuilder('object')
    ->leftJoin('object.children', 'children')
    ->where('SIZE(object.children) = 0')
    ->getQuery()
    ->getResult();