Subquery in From claus in JPA2 Criteria

Question

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?

score 6 · Accepted Answer · answered Jul 20 '12 at 07:39

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.

Hard to say, should ask from ones who designed it. At least it makes building implementation of language easier. Only way around is to write queries without subqueries in FROM clause. — Mikko Maunu, Jul 20 '12 at 14:23

Subquery in From claus in JPA2 Criteria

1 Answers1

Linked