Please have a look at the end of this post (found another but also not working way). This second way should be the better one, if it functions one day ;)
I have an entity which consists of two many-to-many relations: MainEntity has many of As and MainEntity has many of Bs, where A and B is of the same type. Example for the following JQL: If "ob" has got 4 "As" and 5 "Bs" i need the result of -1 in the placeholder "difference" but it allways returns 0. Same using Outer Joins.
String query = "SELECT ob, (COUNT(A) - COUNT(B)) difference" +
+ " FROM MainEntity ob "
+ " JOIN ob.listA A "
+ " JOIN ob.listB B "
+ " GROUP BY ob "
+ " WHERE ob=:PARAM ";
The Following Example is working:
"SELECT COUNT(A) FROM MainEntity ob JOIN ob.listA A "
+ "WHERE ob=:PARAM"
It returns 5 if ob has 5 items in its list "listA". Can anybody help me to construct the right JPQL?
EDIT: This is working:
SELECT SIZE(ob.listA) FROM MainEntity ob WHERE ob=:PARAM
So, I thought that this could work also:
SELECT SIZE(ob.listA) - SIZE(ob.listB) FROM MainEntity ob WHERE ob=:PARAM
But it doesnt work - confusing for me! This JPQL throws following Exception:
Caused by: Exception [EclipseLink-6069] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [ENTITYINFOERWEITERUNG.ID] in this expression has an invalid table in this context.
at org.eclipse.persistence.exceptions.QueryException.invalidTableForFieldInExpression(QueryException.java:722)
I think its funny, because following JPQL throws same Exception:
SELECT SIZE(ob.listA) - SIZE(ob.listA) FROM MainEntity ob WHERE ob=:PARAM
You would need to use subqueries in select clause to get two counts with a single select. Unfortunately JPA doesn't support subqueries in select clause. Your options are either to use two queries or native sql.