Simple group by, without getting back projection object

lt · **Joined:** Wed Apr 20, 2005 5:05 pm **Posts:** 12

Hello,

I think I have a simple problem, but the solution eludes me. First off, I know I can just use HQL to do this. I'll go this route if i need to, but ive built a wrapper around the criteria framework so i can manipulate my result data from outside the hibernate layer.

My Problem: I have a table that rows of data, with a date column...simplified query is below.

Code:

select DATE_FORMAT(a_date, '%Y-%m-%d'), count(*), sum(price) from total group by  to_days(a_date);

Using formulas, I have gotten 90% there. basically the group by consolidates the date column by days. The count pulls the number of rows and the sum adds up all of the consolidated prices for that day. So I need to group by the date column to give me the information back as I need it.

If i use

Code:

criteria.setProjection(Projections.groupProperty("aDate"));

i get back the date...when I really just want to group the rows together to get me back the ROOT_ENTITY object.

criteria.setResultTransformer(Criteria.ROOT_ENTITY)...doesn't work.

Any ideas?

Thx.
LT

CWitherow · **Posted:** Wed Apr 26, 2006 1:11 pm

Are you looking for:
session.createCriteria( Entity1.class ).setProjection(Projections.projectionList().add(Projections.groupProperty("col1")).add(Projections.rowCount()).add(Projections.sum("col2"))).list(); ??

This would return a list of object[] (three elements). You could then set an appropriate ResultTransformer -- ROOT_ENTITY is probably not going to do it as, I assume, your ROOT_ENTITY has additional properties. Take a look at AliasToBeanResultTransformer.

Good luck
Curtis ...