[FIXED] Doctrine 2, association mapping with conditions

Issue

I have a similar problem and DB structure:

Doctrine2 association mapping with conditions

But, I need collections of Article with approved comments:

How to do assotiation mapping, that do without N+1 ?

$articles = $repository->findAllArticlesWithApprovedComments();

    Foreach($articles as $article){
       $article->getTitle();
       foreach($article->getAprovedComments() as $comment){
           $comment->getText();
       }
    }

Criteria worked, if i use lazy load, but its N+1 problem. If I use Eager load (join and addSelect ) - Criteria not work.

If I use this code:

$articles = $em->createQueryBuilder()
            ->from(Article::class,'article')
            ->leftJoin('article.comments','comments')
            ->addSelect('article')
            ->addSelect('comments')
            ->andWhere('comments.approved= :ap ')
            ->setParameter('ap',true)
            ->getQuery()->getResult();

I will receive articles with approved comments, but if the article has 0 comments, it will not fall into the collection of articles.

How to get articles with approved comments, but if there are no comments in the article, the article remains in the collection?

Example: I have in DB:

Article1: [approvedComment, nonAprovedComment]
Article2: [nonAprovedComment]
Article3: [approvedComment]

I need result (with doctrine, non filter in code):

Article1: [approvedComment]
Article2: []
Article3: [approvedComment]

Solution

You can use a join instead of a where condition to filter your collection on the database level.

Your query would then look like this:

$articles = $em->createQueryBuilder()
            ->from(Article::class, 'article')
            ->leftJoin('article.comments', 'comments', 'WITH', 'comments.approved = :ap')
            ->addSelect('article')
            ->addSelect('comments')
            ->setParameter('ap', true)
            ->getQuery()->getResult();

Since this is a left join, it will return all articles, even if they do not have any approved comment.

Answered By - Arno Hilke