Hi,
This has been a lot more challenging than I thought, hopefully I am missing something. I have two classes related via a @ManyToMany - physician, and site. I need to query the physician table to find out how many sites someone has at most.
Here is the code for the relationships, site is the owning side.
Code:
public class Physician extends IdEntity<Short> {
private List<Site> siteList;
@ManyToMany(mappedBy="physicianList")
public List<Site> getSiteList() {
return siteList;
}
}
public class Site extends IdEntity<Short> {
private List<Physician> physicianList;
@ManyToMany(cascade=CascadeType.ALL)
public List<Physician> getPhysicianList() {
return physicianList;
}
}
in native sql i can just do
Code:
select max(counts.theCount) as maxCount from (SELECT count(*) as theCount FROM [tbl_Site_Physician] group by physicianList_id) counts
notice the nested select, hql doesn't go for that apparently. I am able to do this in hql:
Code:
select count(siteList) from Physician as phy left join phy.siteList as siteList group by phy
which gives me a list of the size of each phy's site list, like
4
3
5
7
8
2
out of that list i need the max, so you would think
Code:
select max(counts.theCount) from (select count(siteList) as theCounts from Physician as phy left join phy.siteList as siteList group by phy) theCounts
but I just get a syntax error everytime on the ( , which means this isn't supported or I am just flat out screwing up.
In the meantime, I am getting by with that query order by theCount desc .setMaxResults(1) .uniqueResult() which works, but still I'd like to know the better way
TIA[/quote]
Login
FAQ
Search
