I've got the following entities:

• Exercise, with a collection 'participations' of type Participation.
• Each Participation has a collection of Idea and IdeaReview.
• Idea and IdeaReview each have a submitter property of type Participation.

I want to produce a table listing each exercise and the number of ideas and idea reviews created by its participants. This isn't HQL, but I'd like to write something like this pseudocode:

select e.title, size(e.participations.ideas), size(e.participations.ideaReviews) from Exercise e

For whatever reason (oxygen starvation as a child, perhaps) I can't figure out the actual HQL for this query, even using the interactive Eclipse HQL tools. I'm assuming I have to do something from the other direction, along the lines of:

where i.submitter in elements(e.participations)

.. but keep stumbling on errors, such as "No table 'idea' defined." (The hibernate mappings work just fine in the application, they're not new.)

Can anyone set me straight? Any nudges in the right direction would be helpful.

How about this HQL:

select e.title, size(p.ideas), size(p.ideaReviews) from Exercise e left join e.participations p