sql - Doctrine Search by count on join Table

Question

我的克星有问题:SQL/DQL
我有一个实体:Style 和一个实体 Vote。一种风格可以有很多投票(如您所想)，而一个投票恰好与一种风格相关联。
我现在想搜索投票数少于特定数量的样式(这是用户输入，但假设为 5)。这是我与 一起使用的代码查询生成器

$qb = $this->createQueryBuilder('s');
$qb
    ->select("s")
    ->join('s.votes', 'v')
    ->addSelect("COUNT(v.id) as voteCount")
    ->where("voteCount < ?1")
    ->orderBy('voteCount', "DESC")
    ->setMaxResults(3)
    ->setFirstResult(0)
    ->groupBy('s.id')
    ->setParameter(1, 5);

$query = $qb->getQuery();
$result = $query->getResult();

一旦我尝试执行查询，它基本上说，在 where 子句中不知道我的voteCount。这是确切的错误消息

An exception occurred while executing 'SELECT s0_.active AS active0, s0_.views AS views1, s0_.description AS description2, s0_.name AS name3, s0_.id AS id4, s0_.updated AS updated5, s0_.created AS created6, COUNT(v1_.id) AS sclr7, s0_.battle_id AS battle_id8, s0_.user_id AS user_id9, s0_.voucher_id AS voucher_id10 FROM Style s0_ INNER JOIN Vote v1_ ON s0_.id = v1_.style_id WHERE sclr7 < ? GROUP BY s0_.id ORDER BY sclr7 DESC LIMIT 3 OFFSET 0' with params [1]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sclr7' in 'where clause'

我做错了什么？如何搜索voteCount？

Answer 1

聚合函数的条件，例如 sum和 count应该放在 having 条款。

$qb = $this->createQueryBuilder('s');
$qb->select("s");
$qb->join('s.votes', 'v');
$qb->addSelect("COUNT(v.id) as voteCount");
$qb->orderBy('voteCount', "DESC");
$qb->setMaxResults(3);
$qb->setFirstResult(0);
$qb->groupBy('s.id');
$qb->having("count(v.id) < ?1"); //changed from where
$qb->setParameter(1, 5);
$query = $qb->getQuery();
$result = $query->getResult();