Menu
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?
651
With JPQL, you can use subqueries only in the WHERE and HAVING clauses but not in the SELECT and FROM clause. SQL, of course, allows you to use subqueries also in the SELECT and FROM clause.
The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.
The Jakarta Persistence Query Language (JPQL; formerly Java Persistence Query Language) is a platform-independent object-oriented query language defined as part of the Jakarta Persistence (JPA; formerly Java Persistence API) specification. JPQL is used to make queries against entities stored in a relational database.
This example shows how to use JPQL keyword MEMBER OF to determine whether a value is an element of a collection. The value and the collection members must have the same type.
Here is a solution:
SELECT
u
FROM
User u
WHERE
u.comments.size = (SELECT MAX(u2.comments.size) FROM User u2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
