I'm trying to get historical balances on an account, and to do that I thought maybe I could ask hibernate to calculate those historical amounts and then construct an Account object with the historical balances but the rest of the account object intact - a kind of "group by" that puts the grouped row data into a list instead of aggregating it.

I added a constructor to Account which takes an account and a list of Balance objects. Each Balance corresponds to the balance in a particular currency. Optimistically, I tried passing a subselect as a parameter to this constructor hoping that hibernate would convert the subquery into a list object and then pass that to my constructor.

@NamedQuery(name="account.getAtDate",
query="select new Account(account, (select new Balance(account, je.currency, " +
"sum(case when je.credit = true then je.amount else 0 end), " +
"sum(case when je.credit = false then je.amount else 0 end)) " +
"from account.journalEntries je " +
"where je.timestamp <= :date " +
"group by je.currency " +
"order by je.currency.currencyCode) " +
") " +
"from Account account " +
"where account.business = :business " +
"order by account.name, account.id"),

Am I crazy to attempt this in HQL/EJB-QL? Is it even possible to refactor this query to return a List of Account objects, each with a list of Balances calculated by the query?

For now, it seems like I can create a list of balance objects in HQL without aggregating by account, and then manually pass over the List of Balance to create a list of accounts and add the balances to them, but I have the belief this would perform better if the database did more of the work for me ...