我可以通过检查id对null来优雅地与Doctrine联系吗？

Question

I have implemented the following function, which is very close to what I actually want:

public function loadUserGroupsOfNewsletter($ids, $newsletterID)
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    return $qb->select('ug, gon')
              ->from('MyNameSpace:UserGroup', 'ug')
              ->leftJoin('ug.groupsOfNewsletter', 'gon')
              ->leftJoin('gon.newsletterItem', 'n')
              ->where($qb->expr()->in('ug.id', $ids))
              ->where('n.id = :id')
              ->setParameter('id', $newsletterID)
              ->getQuery()->getResult();
}

I would like to get at least a record for each user group. If there is no newsletter matching the id for a newsletter, then I would like gon to be null for the given result item, but if there is at least a newsletter matching the id, then I would like to have the correct user group and gon record in the result item.

So,

Case 1. The given user group does not have a matching newsletter:

The result item is expected to contain the correct ug and null as gon

Case 2. The given user group has at least a matching newsletter:

There are as many newsletter items as many such matches exist and all result items for this user group will contain valid ug and gon.

I have tried to check n.id against null and the results matched my expectation, so I assume that the following query achieves what I intended, due to the fact that my experiments yielded good results. Am I right in this assumption?

public function loadUserGroupsOfNewsletter($ids, $newsletterID)
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    return $qb->select('ug, gon')
              ->from('MyNameSpace:UserGroup', 'ug')
              ->leftJoin('ug.groupsOfNewsletter', 'gon')
              ->leftJoin('gon.newsletterItem', 'n')
              ->where($qb->expr()->in('ug.id', $ids))
              ->where('(n.id is null) or (n.id = :id)')
              ->setParameter('id', $newsletterID)
              ->getQuery()->getResult();
}

Answer 1

By the looks of it, it should be fine, but I am not sure what are you trying to achieve by returning users both subscribed to this particular newsletter and those that are not subscribed to any?

You may try to to move those predicates to IN statement (WITH in Doctrine):

->leftJoin('ug.groupsOfNewsletter', 'gon')
->leftJoin('gon.newsletterItem', 'n', 'WITH', 'n.id = :id')

Does that make sense? Maybe I misunderstood you intention.

Another thing that caugth my eye is double use of where. According to Doctrine's source, where looks like this:

public function where($predicates)
{
    if (! (func_num_args() === 1 && $predicates instanceof Expr\Composite)) 
    {
        $predicates = new Expr\Andx(func_get_args());
    }

    return $this->add('where', $predicates);
}

and add has a following signature:

public function add($dqlPartName, $dqlPart, $append = false)

In other words, your subsequent where will not be appended but will overwrite the previous one instead.

You should probably use andWhere in order to do what you want.

Hope this helps a bit...