Doctrine query building select MAX

Question

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

Solution 1

It's too late, but I write this for the records.

You can use "as HIDDEN" in SELECT statements to remove a field of the final result, this way you can use it for ordering or grouping without modifying result fields.

In your example:

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

Solution 2

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

Comments

[Braian Mellor]

Please mark it as an answer, also where do you get $limit?