I posted this on StackOverflow and got one answer, which I've included below. But I'm wondering if there is any way to do this without using a group by/having clause.
http://stackoverflow.com/questions/2879 ... s-in-a-setCurrently, I have an HQL query that returns all Members who possess ANY Award from a set of specified Awards:
Code:
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:
Code:
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):
Code:
class Member {
private String name;
private Set<Award> awards;
}
class Award {
private String name;
}
The answer I got on StackOverflow is this:
Code:
select m from Member m left join m.awards as a where a.name in ("Trophy","Ribbon") group by m having count(a)=2
The problem is that I need to use the same HQL in two ways:
(1) to query for paginated data, with a set of 50 results at a time.
(2) to count the total number of results, so that I can show the number of items, number of pages, etc.
When I do a
Code:
select count(distinct m.id) from Member m left join m.awards as a where a.name in ("Trophy","Ribbon");
with the group by clause, calling uniqueResults() throws an exception. For instance, if the regular list() query returns 4 member objects:
Code:
[Member, Member, Member, Member]
Then the count() version of the query returns an array of 4 elements:
Code:
[1,1,1,1]
I assume that is because each "group" has a size of 1. Any ideas how to accomplish this?
Login
FAQ
Search
