SpringData : is it possible to have subqueries in the Query annotation?

Question

I would like to know if it is possible to have subquery in a @Query annotation (org.springframework.data.jpa.repository.Query;)

I am getting a QuerySyntaxException on the first subquery parentesis.

Here is my query

@Query(value="select c1 from ComplaintModel c1, "
+ "(select c2.id, min(cb.termDate) minDate from ComplaintModel c2 "
+ "join c2.complaintBullets cb join cb.status s where s.code = ?1 "
+ "group by c2.id) tmp where c1.id = tmp.id order by tmp.minDate")

Thanks!

Answer 1

No, it is not possible to have subquery in the select clause in JPQL query.

JPQL supports subqueries in WHERE and HAVING clauses. It can be (at least) part of ANY, SOME, ALL, IN, EXIST expressions, and of course it can be used normal conditional expressions:

SELECT a
FROM A a
WHERE a.val = (SELECT b.someval 
               FROM B b 
               WHERE b.someotherval=3)