HQL: Trouble with trivial select with aggregate function

I have very simple data model 开发者_JAVA技巧like this:

create table Company (
    id int primary key,
    name varchar(50),
    street varchar(50)
)

create table Person (
    id int primary key,
    name varchar(50),
    surname varchar(50),
    id_company int foreign key references Company
)

corresponding java classes like this:

class Company {
    int id;
    String name, street;
    List<Person> employees;
    // getters, setters, ctor
}

class Person {
    int id;
    String name, surname;
    Company employer;
    // getters, setters, ctor
}

Now, I 'd like to select Company with most employees with HQL. I don't know how, here's my rubbish pseudo attempt that doesn't work:

from Company c having max(c.employees);

---

EDIT: this worked for me - to get company id and employee count

select c.id, max(c.employees.size) from Company c

To get the company object:

from Company comp
where comp.employees.size = (
  select max(c.employees.size) from Company c
)

If you don't have the hibernate plugin for eclipse yet install it it's great for this as it also displays the generated sql so you can optimize your hql, see http://www.hibernate.org/subprojects/tools.html.

Just thought of something, what happens if two companies CompA and CompB both have the highest number of employees, say 50, i.e. all other companies have less than 50, do you care which company id you get back? Are you more interested in the number 50 or the company id?

---

It's not trivial, actually. I can't think of a way to do what you want with a single query, but you can do it with two queries using something like this:

String hql = "select p.employer.id, count(*) from Person p group by p.employer.id order by count(*) desc";
Query query = session.createQuery(hql);
query.setMaxResults(1);
List<Object[]> list = (List<Object[]>) query.list();
Object[] oa = list.get(0);
Integer companyId = oa[0];
Company company = session.get(Company.class, companyId);

The above code assumes that there is only one Company that has the most employees. It is possible of course for multiple companies to have the same number of employees which also happen to be the maximum.

---

Try:

select company from Company company order by company.employees.size

In code you retrieve only the first element.

query.setMaxResult(1);

---

Check out the Hibernate chapter on HQL, specifically the aggregate function section:

select avg(cat.weight), sum(cat.weight), max(cat.weight), count(cat) from Cat cat