Hibernate HQL and fetching with grouping

Question

We have a hibernate pojo 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?

Answer 1

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.