logo标签列表

Symfony2 Doctrine中的IFNULL查询

symfonydoctrinequery-builderifnull
14

好的，我有这段代码：

SELECT 
IFNULL(s2.id,s1.id) AS effectiveID, 
IFNULL(s2.status, s1.status) AS effectiveStatus, 
IFNULL(s2.user_id, s1.user_id) as effectiveUser,
IFNULL(s2.likes_count, s1.likes_count) as effectiveLikesCount

FROM statuses AS s1
LEFT JOIN statuses AS s2 ON s2.id = s1.shared_from_id
WHERE s1.user_id = 4310
ORDER BY effectiveID DESC
LIMIT 15

我需要将它重写为查询构建器。类似这样吗？

        $fields = array('IFNULL(s2.id,s1.id) AS effectiveID','IFNULL(s2.status, s1.status) AS effectiveStatus', 'IFNULL(s2.user_id, s1.user_id) as effectiveUser','IFNULL(s2.likes_count, s1.likes_count) as effectiveLikesCount');

    $qb=$this->_em->createQueryBuilder()
             ->select($fields)
             ->from('WallBundle:Status','s1')
             ->addSelect('u')
             ->where('s1.user = :user')
             ->andWhere('s1.admin_status = false')
             ->andWhere('s1.typ_statusu != :group')
             ->setParameter('user', $user)
             ->setParameter('group', 'group')
             ->leftJoin('WallBundle:Status','s2', 'WITH', 's2.id=s1.shared_from_id')  
             ->innerJoin('s1.user', 'u')               
             ->orderBy('s1.time', 'DESC')
             ->setMaxResults(15);

     var_dump($query=$qb->getQuery()->getResult());die();

这个错误是

[Syntax Error] line 0, col 7: Error: Expected known function, got 'IFNULL'
是的，就像错误信息所说的那样 - Doctrine查询语言不知道这样的函数。在类似问题的回答中找到了答案http://stackoverflow.com/a/9110213/1727046 - gatisl
@gatisl 是的先生，我看到了，但我不知道...我该如何“安装”它？ - Lukas Lukac
2个回答
40

使用 COALESCE 替代 IFNULL，如下所示

$fields = array('COALESCE(s2.id,s1.id) AS effectiveID','COALESCE(s2.status, s1.status) AS effectiveStatus', 'COALESCE(s2.user_id, s1.user_id) as effectiveUser','COALESCE(s2.likes_count, s1.likes_count) as effectiveLikesCount');

COALESCE函数在参数列表中返回第一个非空值，因此如果A为空且B不为空，则COALESCE(A,B)将返回B。

你救了我的一天！我使用了你的解决方案来替换 ORDER BY 中的 CASE WHEN。 - Eve
4
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接