Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

hibernate

hql

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 
like image 919
Michel Avatar asked Sep 21 '10 13:09

Michel


People also ask

Is empty in HQL?

No. You have to use is null and is not null in HQL.

What is NVL in HQL?

NVL Function - PL/HQL Reference NVL function returns first non-NULL expression.

How can I get HQL query results?

You have to do the following: final String hql = "select app. appkey,app. type from " + getClassName() + " app where app.

Is empty in JPQL?

Queries can use IS EMPTY operator or IS NOT EMPTY to check whether a collection association path resolves to an empty collection or has at least one value. We can use the EMPTY to check if a property is empty. The following JPQL shows how to use EMPTY to get employee withno projects.


2 Answers

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 
like image 26
rebelliard Avatar answered Sep 20 '22 12:09

rebelliard


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
    • 14.10. Expressions
  • JPA 1.0 specification
    • Section 4.6.11 "Empty Collection Comparison Expressions"
like image 98
Pascal Thivent Avatar answered Sep 16 '22 12:09

Pascal Thivent