duangewu5234 2018-03-26 17:46
浏览 49
已采纳

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

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();
}
  • 写回答

1条回答 默认 最新

  • douyouzheng2209 2018-03-26 23:18
    关注

    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...

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 phython如何实现以下功能?查找同一用户名的消费金额合并—
  • ¥15 孟德尔随机化怎样画共定位分析图
  • ¥18 模拟电路问题解答有偿速度
  • ¥15 CST仿真别人的模型结果仿真结果S参数完全不对
  • ¥15 误删注册表文件致win10无法开启
  • ¥15 请问在阿里云服务器中怎么利用数据库制作网站
  • ¥60 ESP32怎么烧录自启动程序,怎么查看客户esp32板子上程序及烧录地址
  • ¥50 html2canvas超出滚动条不显示
  • ¥15 java业务性能问题求解(sql,业务设计相关)
  • ¥15 52810 尾椎c三个a 写蓝牙地址