Is there a way to have a formula property which computes an aggregate from a joined table?

Example. I have an Order which can have many items. Each item has an amount. I want to have a property of the order which is the cache of the sum amounts of all the items associated with the order.

Both the following approaches fail:
<property name="amountCache"
formula="sum(favor.amount) from Item item, Order order where item.order_id = item.id"

sum(favor.amount) from Item item where item.order_id = id


Hibernate version:
2.1
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Hmm. Somehow this got posted before I was ready! Here's a better version:


Is there a way to have a formula property which computes an aggregate from a joined table?

Example. I have an Order which can have many items. Each item has an amount. I want to have a property of the order which is the cache of the sum amounts of all the items associated with the order.

Both the following approaches fail:


This bottoms out into a java.sql.SQLException with malformed SQL. I guess I'm asking Hibernate to do too much?

Is there any other approach to this short of pushing this to a java method?


Hibernate version:
2.1

Name and version of the database you are using:
MySQL 4.0

You must use a valid subselect:



Do not qualify column names from the main entity.

Hmm I tried that at one point.

I'm guessing if you database does not support subselects (mysql 4.0), you will get a SQLException?

sure, of course.