Hibernate version: 3.1.3

Consider this scenario:
- Entity A with one-to-many relations to Entity B and to Entity C (simple parent-child relation).
- Lots of Bs and Cs for each A (hundreds), and lot of As (tens of thousands).
- B and C both have one property, let's call it "value".

What's a good way to do a HQL query to retrive a list of A ordered by an aggregation of "value" (let's say sum) on B or C?

Let's consider the alternatives:
1) Map two formula properties in A that compute the two aggregations. This solution works, but it's pretty expensive (on session.get() when you need write access to A, both subqueries are computed even when not needed).

2) As solution 1, but with lazy="true" and instrumentation. This works too, but unfortunately looking for a single formula fetch the other one too. For two formula properties it can be a good solution, but it's not so good when you have lots of them.

3) Use java business logic to compute the aggregations. This won't work, because you cannot (obviously) order by a java computed property (and you cannot order later: doing that will make you fetch the whole table just to show maybe the first ten As).

4) Use a subselect in the HQL query, something like:
"select a, (select sum(b.value) from B b where b.parent = a) as valsum from A a, order by valsum".
This could be a good solution, but HQL doesn't support order by anything that is not an object property (the HQL Query showed won't work: the valsum alias will not get replaced).

So I'm pretty clueless... what's a good way to leave the A entity unencumbered by heavy subqueries, while having the possibility to order by an aggregation of a property of his children? It's not a very unusual situation.

Thank you for all the help.

Salvatore

Use option 4, but define it as an sql-query. That allows you to order by computed columns.

This is a nice workaround, but not really a solution. I use Hibernate also to abstract myself from physical table structure, and using native SQL queries is something that I try to avoid unless strictly necessary.
I wonder if there's some solution using HQL only (I know that with Criteria Queries you can order by subqueries).

You cannot label computed columns in HQL, unfortunately. You can put subselects in both select and where clauses, so you could simply repeat the subselect, but that's not exactly efficient. For the moment, I think that the sql-query is the way to go; hopefully, "select (subselect) as label" will be added to HQL sooner rather than later.