How do I count the number of rows returned by subquery?

Question

I want to do something like this:

select count(*) from (select ...)

(As it would be in SQL), but in JPA.

Any ideas on how I would do it?

Answer 1

I stumbled upon this issue as well. I would ultimately like to execute the following JPQL:

SELECT COUNT(u)
FROM (
   SELECT DISTINCT u
   FROM User u
   JOIN u.roles r
   WHERE r.id IN (1)
)

But this wasn't possible, also not with criteria API. Research taught that this was just a design limitation in JPA. The JPA spec states that subqueries are only supported in WHERE and HAVING clauses (and thus not in the FROM).

Rewriting the query in the following JPQL form:

SELECT COUNT(u)
FROM User u
WHERE u IN (
   SELECT DISTINCT u
   FROM User u
   JOIN u.roles r
   WHERE r.id IN (1)
)

using the JPA Criteria API like as follows:

CriteriaQuery<Long> query = cb.createQuery(Long.class);
Root<User> u = query.from(User.class);
Subquery<User> subquery = query.subquery(User.class);
Root<User> u_ = subquery.from(User.class);
subquery.select(u_).distinct(true).where(u_.join("roles").get("id").in(Arrays.asList(1L)));
query.select(cb.count(u)).where(cb.in(u).value(subquery));
Long count = entityManager.createQuery(query).getSingleResult();
// ...

has solved the functional requirement for me. This should also give you sufficient insight into solving your particular functional requirement.