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
Just repeating myself...
The code to get the members that have EXACTLY the given collection of awards:
from Member m
where not exists (
from Award a where a.name in {"Trophy", "Ribbon"}
and a not in(
select * from Award a2 where a2.owner = m
)
) and not exists (
from Award a3 where a3.owner = m and a3 not in {"Trophy", "Ribbon"}
)
You can force distinct results by adding a DISTINCT_ROOT_ENTITY result transformer to the query call, IE:
getSession().createQuery(hql).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
I'm having a similar problem, but what I need to do is (following your example) to select all the members, who posess ALL of the awards and no more. So in your example the only correct result would be Sue. Any ideas?
