HQL Query来检查集合的大小是0还是空的 [英] HQL Query to check if size of collection is 0 or empty

问题描述

我尝试生成一个HQL查询，其中包含具有空白委托集合的用户（由OneToMany映射）：

  SELECT u FROM User u JOIN u.appointments uas WHERE u.status = 1 AND（uas.time.end<：date OR size（uas）= 0）
  

我尝试了几种方式（ NOT EXIST ELEMENT（），IS NULL ）
也参见：如何检查收集是否为空在NHibernate（HQL）中？（这不适用于我）

但仍然不是我希望看到的结果或HQL或SQL中的某些错误SERVER

注意：

没有JOIN的查询工作：

 FROM User u WHERE u.status = 1 AND size（u.appointments）= 0
  
  
 
 
 解决 
 
 
另一个JOIN解决了这个问题： / p> 
 
 
  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 
  
 
 
解决方案使用 IS EMPTY 应该工作（我赞成JPQL语法）：
  SELECT u FROM User u WHERE u.status = 1 AND u。约会是空的
  
如果没有，请显示生成的SQL。
 
 
 
引用
 
 
 
 
 
 Hibernate核心参考指南
 
 
 
 
  14.10。表达式 
 
 
 
 
 
 JPA 1.0规范
 
 
 
 
 4.6.11空集合比较表达式
 
 
 
 
 
 
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.

References


Hibernate Core Reference Guide


14.10. Expressions

JPA 1.0 specification


Section 4.6.11 "Empty Collection Comparison Expressions"



                        
这篇关于HQL Query来检查集合的大小是0还是空的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！