The correct way to count related objects using JPQL

Question

What is the correct way to write this JPA request? I just guess when I cannot handle this 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 to use Hibernate and I am getting 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 question title, 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 the numeric value of the 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 double.

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

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

Numeric arguments for these functions can correspond to numerical Java object types, as well as primitive numeric types.

In the special case of 0 you can also use IS EMPTY

4.6.11. Fuzzy Collection Comparison Expressions

The syntax for using the IS EMPTY comparison operator in empty_collection_comparison_expression is as follows:

 collection_valued_path_expression IS [NOT] EMPTY 

This expression verifies the collection, the assigned expression for the collection is empty (i.e. has no elements).

Example:

 SELECT o FROM Order o WHERE o.lineItems IS EMPTY 

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

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