SQL HQL 查询以检查集合大小是否为 0 或为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3760711/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HQL Query to check if size of collection is 0 or empty
提问by Michel
I try to generate a HQL query that include user with a empty appoinment collections(mapped by OneToMany):
我尝试生成一个 HQL 查询，其中包含具有空约会集合的用户（由 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)
我尝试了几种方式 ( NOT EXIST ELEMENT(), IS NULL) 还请参阅：如何检查 NHibernate (HQL) 中的集合是否为空？（这对我不起作用）
but still not the result I want to see or some error in HQL or SQL SERVER
但仍然不是我想看到的结果或 HQL 或 SQL SERVER 中的一些错误
Note:
笔记：
the query without the JOIN works:
没有 JOIN 的查询有效：
"FROM User u WHERE u.status = 1 AND size(u.appointments) = 0"
Solved
解决了
Another JOIN solved the problem:
另一个JOIN解决了这个问题：
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
回答by Pascal Thivent
Using IS EMPTYshould work (I would favor a JPQL syntax):
使用IS EMPTY应该有效（我更喜欢 JPQL 语法）：
SELECT u FROM User u WHERE u.status = 1 AND u.appointments IS EMPTY
If it doesn't, please show the generated SQL.
如果没有，请显示生成的 SQL。
References
参考
- Hibernate Core Reference Guide
- JPA 1.0 specification
- Section 4.6.11 "Empty Collection Comparison Expressions"
- Hibernate 核心参考指南
- JPA 1.0 规范
- 第 4.6.11 节“空集合比较表达式”
回答by rebelliard
Have you taken a look at your generated SQL? Your method works fine here:
您是否查看过生成的 SQL？你的方法在这里工作正常：
// 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
回答by fez
// Hibernate query:
const string hql = "from User u where u.Id = 101 and size(u.Appointments) = 0";
