Doctrine query building select MAX

rat4m3n picture rat4m3n · Apr 24, 2013 · Viewed 61.6k times · Source

I would like to select everything + MAX value and receive only rows having max values.

    $query = $this->createQueryBuilder('s');
    $query->where('s.challenge = :challenge')->setParameter('challenge', $challenge);
    $query->groupBy('s.score');
    $query->getQuery();

    return $query->select('s.*, MAX(s.score) AS max_score')->getQuery()->getResult();

How could I achieve this in doctrine? I am getting an error that * property is not found. I have tried to select them all one by one but no luck either.

Goal is to achieve something like this

SELECT user, challenge, whateverelse, MAX(score) FROM users_scores_table GROUP BY user_id

Please help ;)

Answer

rat4m3n picture rat4m3n · Apr 24, 2013

Here is a final working query

    $query = $this->createQueryBuilder('s');
    $query->select('s, MAX(s.score) AS max_score');
    $query->where('s.challenge = :challenge')->setParameter('challenge', $challenge);
    $query->groupBy('s.user');
    $query->setMaxResults($limit);
    $query->orderBy('max_score', 'DESC');

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