CODEWITHSUNDEEP
sqlhibernatehql
Michel
Michel

Reputation: 9440

HQL Query to check if size of collection is 0 or empty

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

Upvotes: 40

Views: 74136

Answers (4)

Sivaranjani Duraisamy
Sivaranjani Duraisamy

Reputation: 89

The following query will work. The trick is using the LEFT join.

SELECT u FROM User u LEFT JOIN u.appointments uas 
WHERE u.status = 1 AND 
(uas.time.end < :date OR size(u.appointments) = 0)

Upvotes: 0

fez
fez

Reputation: 91

// Hibernate query:
const string hql = "from User u where u.Id = 101 and size(u.Appointments) = 0";

Upvotes: 9

rebelliard
rebelliard

Reputation: 9611

Have you taken a look at your generated SQL? Your method works fine here:

// Hibernate query:
const string hql = "from User u where u.Id = 101 and size(u.Appointments) = 0";


// Generates this working SQL:
select user0_.Id    as Id20_,
       user0_.Name as Name2_20_
from   User user0_
where  user0_.Id = 101
       and (select count(appointment1_.Id_Solicitud)
            from   Appointment appointment1_
            where  user0_.Id = appointment1_.Id_User) = 0

Upvotes: 15

Pascal Thivent
Pascal Thivent

Reputation: 570395

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.

References

  • Hibernate Core Reference Guide
  • JPA 1.0 specification
    • Section 4.6.11 "Empty Collection Comparison Expressions"

Upvotes: 64

Related Questions