mysql - Symfony Group By 子选择错误

Question

在 Symfony 2 中，我尝试进行子选择以从表中获取总计数。我每次都会收到以下错误:SQLSTATE[21000]:基数违规:1242 子查询返回超过 1 行。为什么我的语句中的 GROUP BY 不起作用？

$dql = " SELECT u.id as UserId, (
                          SELECT  count(mtp.id)
                          FROM    Site\MyBundle\Entity\MessageThread mtp
                          WHERE   mtp.status=0
                          GROUP BY mtp.message
                    ) as MessageThreadCountPending
                    FROM      Site\UserBundle\Entity\Entity\User u
                    WHERE     u.id=:id
                    ";

消息线程表示例

id | message | status | text 
-------------------------------------------
1  | 1       | 0      | Blah blah blah
2  | 2       | 0      | Blah blah blah
3  | 2       | 0      | Blah blah blah
4  | 3       | 0      | Blah blah blah

期望的结果是 3

感谢反馈!

Answer 1

一个简单的修复方法是删除子查询中的group by:

SELECT u.id as UserId,
       (SELECT count(mtp.id)
        FROM Site\MyBundle\Entity\MessageThread mtp
        WHERE mtp.status = 0
       ) as MessageThreadCountPending
FROM Site\UserBundle\Entity\Entity\User u
WHERE u.id = :id;

虽然这解决了问题，但我怀疑您想要在子查询中使用某种相关子句。我猜是这样的:

        WHERE mtp.status = 0 and mtp.user_id = u.id