Count function in HQL causing null results?

Question

I have the following HQL query that is attempting to return 2 object instances as well as an aggregate count based upon a 3rd object instance.

SELECT
 client,
 clientCampaign,
 count( formData )
FROM
 FormData as formData
 JOIN formData.deliveryResults as deliveryResults
 JOIN formData.leadForm as leadForm
 JOIN leadForm.campaignForms as campaignForms
 JOIN campaignForms.clientCampaignForms.clientCampaign as clientCampaign
 JOIN clientCampaign.client as client
WHERE
 client.id = ?
GROUP BY
 client.id, clientCampaign.id

The results always return with the clientCampaign instance being null. However, if I remove the count( formData ) line from the SELECT clause, the clientCampaign instance is no longer null. I have checked the data set and there are 5 total formData records ... 3 for one clientCampaign and 2 for another. So, I believe this query should be returning 2 results, both with non-null clientCampaign instances.

Can anybody tell if there is something obvious I am missing here?

TIA

Answer 1

1. Can you post your object mappings? It's hard to say without them but it feels (judging by names) that you're walking up the hierarchy (from collection to owner) instead of down, which is somewhat unnatural using Hibernate.

2. JOIN formData.deliveryResults as deliveryResults is unnecessary (it's not used anywhere). Most other joins may be unnecessary as well - for "to-one" associations you can write properties using nested dot notation without explicit joins.

3. Your GROUP BY clause only lists IDs whereas you're selecting full entities (client and clientCampaign). That's not going to work - you have to explicitly list fields to group by.

Depending on your mappings, you may be able to rewrite this query using parent-to-child joins; you may than be able to use size() function on your deliveryResults collection without need for GROUP BY.

Answer 2

Before other comment, I like the way you present your HQL request at first, with each JOIN on its own line. It is very readable. I suggest you refactor the clientCampaign the same way, even if you don't use the aliases... This might also change some results (who knows what code could be triggered in the inners of Hibernate with your original request !?).

You request seem to move between tables going up and down (judging from names, if they end with an 's' or not). It is a bit hard to read. I would probably be easier for you and for us to switch to a single order (for example going down, starting from leadForm). In particular, we would detect other "joins" going up, and examine them more carefully (I'm suspicious at clientCampaignForms.clientCampaign...)