As stated in the JPA 2.1 specification the count function can be applied to an association field in the select clause:
[...] The path expression argument to COUNT may terminate in either a state field or a association field, or the argument to COUNT may be an identification variable. (JSR 338, section 4.8.5)
So I expect such a query to be valid JPQL:
SELECT count(i.courses) FROM Instructor i GROUP BY i
(instructor entity has bidirectional one-to-many relationship to course entity)
Jet if i try to execute the statement on Hibernate (4.3.11) as a JPA provider it fails (i.e. SQLGrammarException) but EcplipseLink can execute it. So is Hibernate not compliant or did I get it wrong?
The correct JPQL way of getting the size of a collection field is to use
SELECT SIZE(i.courses) FROM Instructor i GROUP BY i
The JPQL you state is likely not portable, since the JPQL BNF says
aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) |
COUNT ([DISTINCT] identification_variable | state_field_path_expression |
single_valued_object_path_expression)
and it does not include collection_valued_path_expression expressions in that, so I would use the SIZE
function myself