33

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
Community
  • 1
  • 1
Michel
  • 8,310
  • 13
  • 41
  • 56

3 Answers3

55

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"
Pascal Thivent
  • 535,937
  • 127
  • 1,027
  • 1,106
  • unfortunately ' "FROM User u JOIN u.appointments uas WHERE u.status = 1 AND uas is empty" ' doesn't work. It gives me no users. And there are a lot of users without appointments – Michel Sep 21 '10 at 13:44
  • mm your suggestion works but i needed that JOIN. I'm looking for the method to use the size or is empty in combination with the Join, Thx for your support – Michel Sep 21 '10 at 13:52
  • 1
    @michel Why do you need the join? Your query with the JOIN will only return users with matching values in the join condition (i.e. users *with* appointments). – Pascal Thivent Sep 21 '10 at 13:54
  • @Pascal I need it because I need all users with appointments not older than 10 days for example and with no appointments – Michel Sep 21 '10 at 14:00
  • @michel You might need a LEFT JOIN then, not an INNER JOIN. Your current query (without the condition on the appointments size) is not doing what you want. – Pascal Thivent Sep 21 '10 at 14:01
  • @Pascal something like this: --> SELECT u FROM User u LEFT JOIN u.appointments uas WHERE u.status = 1 AND (uas.slot.end <= :date1 OR size(u.appointments) = 0 ) ORDER BY u.name asc SELECT u FROM User u LEFT JOIN u.appointments uas WHERE u.status = 1 AND (uas.slot.end <= :date1 OR size(uas) = 0 ) ORDER BY u.name asc – Michel Sep 21 '10 at 14:12
  • @michel It's hard to discuss this in a comment box but do you agree that `SELECT u FROM User u JOIN u.appointments uas WHERE u.status = 1` won't return any record without appointments (and is thus not what you want?) – Pascal Thivent Sep 21 '10 at 14:19
  • `BTW: when using "IS EMPTY" no STringIndexOutOfBoundsException but only 2 users instead of thousands` Show the generated SQL. – Pascal Thivent Sep 21 '10 at 14:21
  • @Pascal I double agree with you, difficult to discus the problem here and the SELECT with the INNER JOIN doesn't give the correct result, But how can I solve this? Do I need an IN query? – Michel Sep 21 '10 at 14:32
  • @michel It might help to see the generated SQL for analysis. But somehow, this is a different question. Maybe opening another question for this purpose would be better. – Pascal Thivent Sep 21 '10 at 14:36
  • I Solved the problem. I need another JOIN in my HQL query: 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 Thanks for helping with the Solution. The suggestion LEFT JOIN helps me a lot. THX – Michel Sep 21 '10 at 15:22
  • @michel Well, glad this little discussion helped to find the solution. Well done. – Pascal Thivent Sep 21 '10 at 15:39
15

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
rebelliard
  • 9,342
  • 6
  • 43
  • 78
9
// Hibernate query:
const string hql = "from User u where u.Id = 101 and size(u.Appointments) = 0";
YCF_L
  • 49,027
  • 13
  • 75
  • 115
fez
  • 91
  • 1
  • 2