symfony querybuilder group function

Question

i want get rows individually with condition that sum of prizes equals to my parameter (price) actually i want limit number of rows in prize entity but i get this error:

SQLSTATE[HY000]: General error: 1111 Invalid use of group function

this is my query :

$qb = $this->createQueryBuilder('up')
        ->join('up.prize','prize')
        ->select()
        ->where('up.user = :user')
        ->andWhere('SUM(prize.prizeValue) <= :price')
        ->setParameters(['user'=>$user , 'price'=>$price])
        ->getQuery()
        ->getResult();
    return $qb;

score 0 · Answer 1 · answered Jan 07 '19 at 14:28

0

You can't use aggregate function in WHERE clause.

Why are aggregate functions not allowed in where clause

Try this:

$qb = $this->createQueryBuilder('up')
        ->join('up.prize','prize')
        ->select('up')
        ->where('up.user = :user')
        ->having('SUM(prize.prizeValue) <= :price') // Replace with `having`
        ->setParameters(['user'=>$user , 'price'=>$price])
        ->groupBy('up') // `Group by` because you're using an aggregate
        ->getQuery()
        ->getResult();
    return $qb;

answered Jan 07 '19 at 14:28

domagoj

906
1
8
20

it works . but main problem is if condition is true it gives me all of prizes . i want restrict rows based on having condition . for example if price = 3000 . it must give me 3 row – sinak Jan 07 '19 at 15:00
This is as far as I can tell how to fix the problem you had. Only you know what parameters are you setting, what records you have in the base... Maybe it's the condition, maybe it's the database - I suggest you double-check all of those. – domagoj Jan 07 '19 at 17:51

score 0 · Answer 2 · answered Jan 07 '19 at 14:28

0

You have to move the Where condition to the HAVING clause

$qb = $this->createQueryBuilder('up')
        ->join('up.prize','prize')
        ->where('up.user = :user')
        ->having('SUM(prize.prizeValue) <= :price')
        ->groupBy('up.id')
        ->setMaxResults(5);
        ->setParameters(['user'=>$user , 'price'=>$price]);

    return $qb->getQuery()->getResult();

answered Jan 07 '19 at 14:28

famas23

2,072
4
17
53

it works . but main problem is if condition is true it gives me all of prizes . i want restrict rows based on having condition . for example if price = 3000 . it must give me 3 row . without any setMaxResult – sinak Jan 07 '19 at 15:08

symfony querybuilder group function

2 Answers2