Correct way to count associated objects using JPQL

Question

What is the correct way to write this JPA query? I am just guessing as I cant work it out or find it in my JPA book.

Query query=em.createQuery("select m from Meeting m where count(m.attendees) = 0");
return query.getResultList();

I am currently trying this with Hibernate and I get a mysql error!

ERROR org.hibernate.util.JDBCExceptionReporter - You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near ')=0' at line 1

Answer 1

To strictly answer the title of the question, use SIZE:

Query query=em.createQuery("select m from Meeting m where size(m.attendees) = 0");
return query.getResultList();

From the JPA specification:

4.6.16.2 Arithmetic Functions

functions_returning_numerics::=
ABS(simple_arithmetic_expression) |
SQRT(simple_arithmetic_expression) |
MOD(simple_arithmetic_expression, simple_arithmetic_expression) |
SIZE(collection_valued_path_expression)

The ABS function takes a numeric argument and returns a number (integer, float, or double) of the same type as the argument to the function.

The SQRT function takes a numeric argument and returns a double.

The MOD function takes two integer arguments and returns an integer.

The SIZE function returns an integer value, the number of elements of the collection. If the collection is empty, the SIZE function evaluates to zero.

Numeric arguments to these functions may correspond to the numeric Java object types as well as the primitive numeric types.

In the particular case of 0, you could also use IS EMPTY

4.6.11 Empty Collection Comparison Expressions

The syntax for the use of the comparison operator IS EMPTY in an empty_collection_comparison_expression is as follows:

collection_valued_path_expression IS [NOT] EMPTY

This expression tests whether or not the collection designated by the collection-valued path expression is empty (i.e, has no elements).

Example:

SELECT o
FROM Order o
WHERE o.lineItems IS EMPTY

If the value of the collection-valued path expression in an empty collection comparison expression is unknown, the value of the empty comparison expression is unknown.

I would test both to see which one is the most efficient (check the query plan).