Select record with max value from each group with Query DSL

Question

I have a score table where I have players scores, and I want select unique records for each player with the biggest score.

Here is the table:

id | player_id | score | ...
1  | 1         |  10   | ...
2  | 2         |  21   | ...
3  | 3         |  9    | ...
4  | 1         |  30   | ...
5  | 3         |  2    | ...

Expected result:

id | player_id | score | ...
2  | 2         |  21   | ...
3  | 3         |  9    | ...
4  | 1         |  30   | ...

I can achieve that with pure SQL like this:

SELECT *
FROM player_score ps
WHERE ps.score = 
(
    SELECT max(ps2.score)
    FROM player_score ps2
    WHERE ps2.player_id = ps.player_id
) 

Can you tell me how to achieve the same query with query dsl? I found some solutions with JPASubQuery but this class doesn't work for me (my IDE cannot resolve this class). I am using querydsl 4.x. Thank you in advance.

Answer 1

JPASubQuery has been removed in querydsl 4. Instead use JPAExpressions.select. Your WHERE clause should look something like this:

.where(playerScore.score.eq(JPAExpressions.select(playerScore2.score.max())
                            .from(playerScore2))
                            .where(playerScore2.playerId.eq(playerScore.playerId)))