Is JPA subquery in FROM clause possible?

Question

I'm having a little problem with JPA. Consider this scenario: Table A (id_a) | Table B (id_b, id_a) What I need is a query like this: ``` Select a.*, c.quantity from A as a, (Select Count(*) as quantity from B as b where b.id_a = a.id_a) as c; ``` The thing is that I want to use a jpa query and not a native query, something like this: ``` Select a, c FROM A a, (Select Count(b) FROM B b where a.idA = b.a.idA) c; ``` So then I could iterate from the result (a list of Object[] with a and c in each node) and then assign a.quantity = c; I repeat, I don't want to use native query, but I found no other way than use redundant data, and add another column to A called Quantity and every time I insert and delete from B, update this column in A. Please help, I read somewhere that JPA doesn't accept subqueries in Form clause, so, what can I do ? Thanks a lot !

Accepted Answer

JPA does not support sub-selects in the FROM clause but EclipseLink 2.4 current milestones builds does have this support.

See, http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause

You can probably rewrite the query with just normal joins though.

Maybe,

```
Select a, size(a.bs) from A a
```

or

```
Select a, count(b) from A a join a.bs b group by a
```

Is JPA subquery in FROM clause possible?

Solution

Problem