Hi there,
I'm using Hibenate 3.2.1 GA with MySQL 5 database and I'm trying to do a grouped sum of 2 columns joined by 4 tables,
its easy to do in the SQL select statement below:

SELECT crate.type, SUM(stackedproduct.no_of_crates) FROM crate, stackedproduct, orderproduct, product
WHERE
stackedproduct.orderproduct_id = orderproduct.id AND
orderproduct.product_id = product.id AND
product.crate_id = crate.id
GROUP BY crate.type;

I can't seem to do this in Hibernate however, I think I am not defining the associations between the tables correctly:

Criteria stackedProductsCriteria = HibernateUtil.getSessionFactory().getCurrentSession().createCriteria(StackedProduct.class);
stackedProductsCriteria = stackedProductsCriteria.createCriteria("orderProduct");
stackedProductsCriteria = stackedProductsCriteria.createCriteria("product");
stackedProductsCriteria = stackedProductsCriteria.createCriteria("crate");

stackedProductsCriteria.setProjection(Projections.projectionList()
.add(Projections.groupProperty("orderProduct.product.crate.type"), "type")
.add(Projections.sum("noOfCrates"), "noOfCrates"));

List <CrateStatsBean> list = stackedProductsCriteria.setResultTransformer(Transformers.aliasToBean(CrateStatsBean.class)).list();


But I get the error:
Exception in thread "main" org.hibernate.QueryException: could not resolve property: orderProduct.product.crate.type of: com.myproj.hibernate.pojo.StackedProduct
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1310)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getType(CriteriaQueryTranslator.java:477)
at org.hibernate.criterion.PropertyProjection.getTypes(PropertyProjection.java:36)
at org.hibernate.criterion.AliasedProjection.getTypes(AliasedProjection.java:37)

The member association of the stackedProduct object "orderProduct.product.crate.type" is correct though (at least in java), so it seems that I am not defining the joins between the tables clearly enough for Hibernate. I've tried all kinds of variations and also with .createAlias() for each table and that doesn't do it either. I have been able to do this successfully before joining 2 tables, but I'm can't do it with 4. Does anyone have any ideas?

Thanks for any help/suggestions
Rory

have you tried, with createAlias and then, instead of:



use



?

Hi Dominicus,
I got in working thanks to you. Here is the working solution I have now:



I explicitly set all the associations with createAlias() as you can see and then just used "crate.type" in the Projections.groupProperty() as you suggested.
Thanks a lot for your help, I really appreciate it.
Rory