HQL来获取拥有集合中所有项目的元素 [英] HQL to get elements that possess all items in a set

问题描述

目前，我有一个HQL查询，返回所有拥有 ANY 奖的所有会员：

 来自成员m左加入m.awards作为a（Trophy，Ribbon）中的a.name; 
  

我现在需要的是HQL，它将返回拥有 ALL 奖项中指定的奖项。因此，假设这个数据：

  Joe拥有Trophy，Medal 
 Sue有奖杯，彩带
 Tom有奖杯，彩带，勋章
  

上面的查询将返回乔，苏和汤姆，因为所有三个至少拥有奖杯或丝带之一。但是我只需要返回苏和汤姆，因为他们是唯一拥有所有特定奖项（奖杯和彩带）的人。

这是类结构（简化）：

  class成员{
私人字符串名称; 
私人设置< Award>奖励; 
} 
 class Award {
私人字符串名称; 
} 
  

解决方案

  select m from Member m left join m.awards as a where a.name in（Trophy，Ribbon）group by m have count（a）= 2 
  

Currently, I have an HQL query that returns all Members who possess ANY Award from a set of specified Awards:

from Member m left join m.awards as a where a.name in ("Trophy","Ribbon");

What I now need is HQL that will return all Members who possess ALL Awards specified in the set of Awards.

So, assuming this data:

Joe has Trophy, Medal
Sue has Trophy, Ribbon
Tom has Trophy, Ribbon, Medal

The query above would return Joe, Sue, and Tom because all three possess at least one of Trophy or Ribbon. But I need to return only Sue and Tom, because they are the only ones who possess all of the specified awards (Trophy and Ribbon).

Here's the class structure (simplified):

class Member {
  private String name;
  private Set<Award> awards;
}
class Award {
  private String name;
}

解决方案

select m from Member m left join m.awards as a where a.name in ("Trophy","Ribbon") group by m having count(a)=2

这篇关于HQL来获取拥有集合中所有项目的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！