JPQL with subquery to select max count

Question

I'm trying to write a jpql query to select the user with the most comments. If two users have the same number of comments I want to select both.

I tried this, something like this:

SELECT
  c.user, COUNT(c.id) as commentCount 
FROM 
  Comment c
WHERE
  commentCount = (SELECT MAX(SIZE(user.comments)) FROM User user)
GROUP BY 
  c.user

and this:

SELECT
  c.user
FROM 
  Comment c
GROUP BY 
  c.user
HAVING
  COUNT(c) = (SELECT MAX(SIZE(user.comments)) FROM User user)

Neither approach works. What do I need to do here?

Answer 1

Here is a solution:

SELECT
  u
FROM 
  User u
WHERE
  u.comments.size = (SELECT MAX(u2.comments.size) FROM User u2)