Marc Morales Valldepérez February 2016

Doctrine2 Limit the rows to sum in a query

I have a query that return a sum, but I want to limit the number of rows to query, but is not working.

This is my query:

    $qb = $this->createQueryBuilder('result')
        ->select('SUM(result.generalPoints) AS generalPoints, SUM(result.coefficient) AS coefficient')
        ->join('result.inscription', 'inscription', Join::WITH, 'inscription.user = :user')
        ->join('inscription.race', 'race')
        ->join('inscription.category', 'category', Join::WITH, 'category.generalRanking = true')
        ->join('race.event', 'event')
        ->join('event.competitionSeason', 'competitionSeason', Join::WITH, 'competitionSeason = :competitionSeason')
        ->orderBy('generalPoints', 'DESC')
        ->addOrderBy('coefficient', 'DESC')
        ->setParameter('competitionSeason', $competitionSeason)
        ->setParameter('user', $user);

    if (isset($limit)) {
        $qb->setMaxResults($limit);
    }

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

Some idea ?

Thanks

Answers


Jan Mares February 2016

As sum is an aggregation function its result is one row, the limit statement is applied afterwards. You need create subquery producing multiple rows with generalPoints and coefficient, limit number of rows in this subquery and use the aggregation function in the wrapping query.

Post Status

Asked in February 2016
Viewed 2,148 times
Voted 4
Answered 1 times

Search




Leave an answer