Subquery in From claus in JPA2 Criteria
criteria-api, jpa-2.0, querydsl
Solution
You cannot use subquery in FROM clause with JPA 2 Criteria API. It does have same limitations as JPQL does. In JPA 2 specification following is said about subqueries in JPQL:
Subqueries may be used in the WHERE or HAVING clause.
And what is said about subqueries in Criteria API leads to same conclusion:
Both correlated and non-correlated subqueries can be used in restriction predicates. A subquery is constructed through the creation and modification of a Subquery object.
A Subquery instance can be passed as an argument to the all, any, or some methods of the CriteriaBuilder interface for use in conditional expressions.
A Subquery instance can be passed to the CriteriaBuilder exists method to create a conditional predicate.
Problem
I have a data model where an account can have multiple users: ``` class Account { Long id; } class User { Long id; @ManyToOne Account account; } ``` I'd like to do the following query which displays the number of users of each account: ``` select Account.id, NumUsers.num from Account, (select Account.id as account_id, count(User.id) as num from User join Account on User.account_id=Account.id group by Account.id) as NumUsers where Account.id=NumUsers.account_id; ``` I know I can re-write this specific query as: ``` select Account.id, count(User.id) from Account join User on User.account_id=Account.id group by Account.id ``` But I plan to create more complicated queries for reports that require more than one group by. I read here about the correct approach to multiple group by. How can I create my query using JPA2 Criteria API?