Hibernate HQL and fetching with grouping

We have a hibernate 开发者_如何学Cpojo Reporting/ReportingID that has several properties that contain counts, dates, actions, and devices. The device property mapped to a hibernate pojo called Device (note: I want it to fetch the device so I don't have to query it separately).

So what I have is a HQL that looks like this:

"SELECT sum(report.deviceTotal), sum(report.settledPricePerDownloadExpense), report.id.device, avg(report.settledPricePerDownloadExpense), report.id.dCampaignActionTypeId " +
"FROM Reporting report " +
"WHERE report.id.dCampaignReportDate between :startDate and :endDate " +
"AND report.id.dCampaignActionTypeId in (:actionIds) " +
"AND report.id.dCampaign.dCampaignId in (:campaigns) " +
"GROUP by report.id.dCampaignActionTypeId, report.id.device " +
"ORDER by 1";

I though that that would work, but I get this error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column 'dbo.device.device_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)

What I get from this is is that I have to list every column in the device table in the group by clause which seems wacky?

Writing

group by something

in HQL where something is an entity gets translated into

group by entity_id1, entity_id2 ...

in SQL where entity_id1, entity_id2, etc. are columns to which entity's id properties are mapped. In case of simple (rather than composite / component) identifier there would only be a single column.

This works fine for regular queries and presents obvious problems for "join fetch" selects. There are no workarounds - you will need to explicitly list all properties individually under group by.

The other approach (which may or may not be suitable - impossible to say without knowing a lot more about your model) is to avoid using join fetch and instead cache your entities (via Session's and / or 2nd level cache) thus retrieving (and grouping by) only entity id.