获得“＃1111 - 组功能的无效使用”没有在MySQL中使用GROUP [英] Getting "#1111 - Invalid use of group function" without using GROUP in MySQL

问题描述

    $qb = $this->createQueryBuilder('t');

    return $qb
        ->join('t.customers', 'c')
        ->where($qb->expr()->eq('t.user', $user->getId()))
        ->andWhere($qb->expr()->gt($qb->expr()->count('c'), 0))
        ->orderBy('t.name')->getQuery()->getResult();

上述查询（Doctrine2生成一个）给我这个错误：

The above query (Doctrine2 generated one) is giving me this error:

＃1111 - 组功能使用无效

但奇怪的是我没有使用 GROUP BY 。非常感谢任何帮助，谢谢。

but the strange thing is i'm not using GROUP BY. Any help is much appreciated, thanks.

SELECT t0_.id AS id0,
       t0_.slug AS slug1,
       t0_.name AS name2,
       t0_.description AS description3,
       t0_.user_id AS user_id4
FROM tag t0_ 
    INNER JOIN customers_tags c2_ ON t0_.id = c2_.tag_id
        INNER JOIN customer c1_ ON c1_.id = c2_.customer_id
WHERE t0_.user_id = 1 AND COUNT(c1_.id) > 0
ORDER BY t0_.name ASC

推荐答案

您正在不允许的where子句中使用聚合函数 count（）。

You are using an aggregate function count() in the where clause which is not allowed.

聚合函数的条件需要进入HAVING子句

Conditions on aggregate functions need to go into a HAVING clause

....
WHERE t0_.user_id = 1
HAVING count(c1_.id) > 0

当然，您需要使用GROUP BY来获取正确的结果（尽管MySQL将可能让你不要使用GROUP BY - 但结果是不可预测的）

And of course you'll need to use a GROUP BY to get correct results (although MySQL will probably let you get away with not using a GROUP BY - but then the results are unpredictable)

这篇关于获得“＃1111 - 组功能的无效使用”没有在MySQL中使用GROUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！