I try to generate a HQL query that include user with a empty appoinment collections(mapped by OneToMany):
SELECT u FROM User u JOIN u.appointments uas WHERE u.status = 1 AND (uas.time.end < :date OR size(uas) = 0)
I tries it on several manners (NOT EXIST ELEMENT(), IS NULL
)
also see: How to check if the collection is empty in NHibernate (HQL)? (This doesn't work for me)
but still not the result I want to see or some error in HQL or SQL SERVER
Note:
the query without the JOIN works:
"FROM User u WHERE u.status = 1 AND size(u.appointments) = 0"
Solved
Another JOIN solved the problem:
SELECT u FROM User u LEFT JOIN u.appointments pas1 LEFT JOIN pas1.slot t WHERE u.status = 1 AND t.end <= :date1 OR t.end IS NULL ORDER BY u.name asc
Using IS EMPTY
should work (I would favor a JPQL syntax):
SELECT u FROM User u WHERE u.status = 1 AND u.appointments IS EMPTY
If it doesn't, please show the generated SQL.