Search code examples
hibernatejparepositoryhqljpql

JPARepository JPQL @query ORDER BY (ERROR: expecting CLOSE, found 'null' )

.im using JPARepository + Hibernate, and ihave the next query wuth a subquery that order sub-result by a number or registers found.

 @Query("SELECT t0 FROM TextFragment t0 WHERE t0.id = "
+ "(SELECT t.id FROM TextFragment t JOIN t.fragment f JOIN t.reactionsTextFragments r "
+ "WHERE f.parent.id = :idParent AND t.codStateTextFragment = '001' "
+ "GROUP BY r.textFragment ORDER BY COUNT(r.textFragment) DESC, t.updatedDate ASC)")

And i have the nex error

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found 'null' near line 1, column 254 [select count(t0) FROM com.treebuk.model.TextFragment t0 WHERE t0.id = (SELECT t.id FROM com.treebuk.model.TextFragment t JOIN t.fragment f JOIN t.reactionsTextFragments r WHERE f.parent.id = :idParent AND t.codStateTextFragment = '001' GROUP BY r.textFragment]

Why the query doesn't read the ORDER BY clause and the final of Query? i dont understand...Anybody can help me?

Solution

  • In JPQL, a subquery cannot have an ORDER BY. As per the BNF

    subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause]

    Consequently your query is invalid. Shame the error message doesn't state explicitly what the problem is though.