CODEWITHSUNDEEP
sqlsymfonyjoindoctrine-ormdql
Loki
Loki

Reputation: 113

Doctrine Search by count on join Table

I have a problem with my nemesis: SQL/DQL I have an entity: Style and an entity Vote. A Style can have a lot of Votes (as you can imagine) and one Vote is linked to exactly one Style.

I now want to search for Styles that have less Votes than a Specific Amount (it's user input, but let's say it is 5). This is the code I use with the querybuilder

$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();

Once I try to execute the query, it basically says, that i voteCount is not known in the where clause. Here is the exact Error Message

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'

What did I do wrong? How can I search vor the voteCount?

Upvotes: 5

Views: 6283

Answers (2)

HarmonicApps
HarmonicApps

Reputation: 88

You cannot reference a column alias in the WHERE clause.

Change

$qb->where("voteCount < ?1");

To

$qb->where("COUNT(v.id) < ?1");

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

Conditions for aggregate functions such as sum and count should be put in a having clause.

$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();

Upvotes: 8

Related Questions