Translate SQL query to Doctrine DQL (join and group by)

Question

I need to translate SQL query to Doctrine DQL.

SELECT g.id, g.round_id, g.score, g.prize_id
FROM game g
INNER JOIN (
    SELECT prize_id, MAX(score) score
    FROM game
    GROUP BY prize_id
) g2 
ON g.prize_id = g2.prize_id AND g.score = g2.score
ORDER BY prize_id DESC

How to do that?

Answer 1

I have find out how to do it in Doctrine DQL:

return $this->createQueryBuilder('g')
    ->leftJoin('App\Entity\Quizz\Game', 'g2', 'WITH', 'g.prize = g2.prize AND g.score < g2.score')
    ->andWhere('g2.prize IS NULL')
    ->getQuery()
    ->getArrayResult();