Search code examples
symfonydoctrine-ormassociationsone-to-manyquery-builder

Doctrine2 QueryBuilder: How to filter out entities having zero count for a OneToMany

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

I want to select all objects which have no children.

I'm stuck with various errors: SingleValuedAssociationField expected, Cannot add Having sth on non result variable, 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?

Solution

  • There is a selector called SIZE(), which should do the trick. More to read here.

    Try something like this:

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