pooler pooler - 5 months ago 28
MySQL Question

DOCTRINE2 oneToMany relationship with condition - no result

for example:
I have entity Post ... Post have collection of entity Comment - relationship is oneToMany
Comments can be deleted by parameter deletedAt, which is default NULL
Comment have another collection of entity B - relationship is oneToMany

I made optimalization for querybuilder:

$qb = $this->createQueryBuilder('post');
$qb->select('post, comments, objectsOfB')
->andWhere('post.id = :id')->setParameter('id', $postId)
->leftJoin('post.comments', 'comments')
->andWhere('comments.deletedAt is NULL')
->leftJoin('comments.objectsOfB', 'objectsOfB');



  • this SQL works, if all comments are not deleted

  • when all comments are deleted, then I have no result



how to solve it ?

Answer

Move the deletedAt check to the join:

$qb = $this->createQueryBuilder('post');
$qb->select('post, comments, objectsOfB')
    ->andWhere('post.id = :id')->setParameter('id', $postId)
    ->leftJoin('post.comments', 'comments', 'WITH', 'comments.deletedAt is NULL')
    ->leftJoin('comments.objectsOfB', 'objectsOfB');
Comments