JPQL query with count function on an association field

Question

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?

Neil Stockton Neil Stockton · Accepted Answer · 2016-10-03T18:28:10

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

JPQL query with count function on an association field

1 Answers