Hibernate version: 3.0.5

Mapping documents:


Name and version of the database you are using: MySQL

HQL :

Hibernate: /* select StudentEnrollment.enquiry.discoverySources.id,StudentEnrollment.enquiry.newsPapers.id,count(distinct StudentEnrollment.enquiryId) from StudentEnrollment as StudentEnrollment group by StudentEnrollment.enquiry.discoverySources.id,StudentEnrollment.enquiry.newsPapers.id */


Generated SQL (show_sql=true):
select discoverys3_.Id as col_0_0_, newspaper6_.Id as col_1_0_, count(distinct studentenr0_.EnquiryId) as col_2_0_ from studentenrollment studentenr0_, studentenquiry studentenq1_, enquirydiscoverymapping discoverys2_, discoverysourcemaster discoverys3_, enquirynewspaper newspapers5_, newspapermaster newspaper6_, enquirydiscoverymapping discoverys8_, discoverysourcemaster discoverys9_, enquirynewspaper newspapers11_, newspapermaster newspaper12_ where studentenq1_.Id=newspapers11_.EnquiryId and newspapers11_.NewsPaperId=newspaper12_.Id and studentenq1_.Id=discoverys8_.EnquiryId and discoverys8_.SourceId=discoverys9_.Id and studentenq1_.Id=newspapers5_.EnquiryId and newspapers5_.NewsPaperId=newspaper6_.Id and studentenq1_.Id=discoverys2_.EnquiryId and discoverys2_.SourceId=discoverys3_.Id and studentenr0_.EnquiryId=studentenq1_.Id group by discoverys9_.Id , newspaper12_.Id


Note that the groupby happens on discovers9_.Id, newspaper12_.Id and not on discovery3_.Id, newspaper6_.Id ( which are a part of the select ). The side effect of this is that grouping is only partial.

For example: for the pair (1,1) - count is 10. Then it repeats again for pair(1,1) count is shown as 25. So to get the real count, I need to add up counts for the same pair. The same pair is repeated more than once which should not be the case!

Is this a bug? I tried to introduce 'as x' and 'as y' in the select fields and tried to group by x,y - but it throws an exception failing to recognize x and y as columns! :(

any suggestions?

Thanks.
Raghu

Implicit joins like that get very complex very quickly. I recommend experimenting with explicit joins. How aboutI don't know if that's exactly what you want, but when you do this, you restrict Hibernate's degrees of freedom and hopefully this will get you closer to where you need to be.

Thanks Tenwit.
Your suggestion helped me solve my problem.

But just wondering why would I need to explicitly mention the joins.

If i say select x.y.z - cant hibernate be smart enough to infer the joins needed? That way my HQLs will be lot more simpler! :-)

It can, but the problem with implicit joins comes when you refer to things part-way down the implicit join chain. For example:This is not the same as this join:The first query, with the implicit joins, is "give me any x that has any y with this name, and that has any y (same or different) that has a z with this other name". Whereas the second query means "give me any x that has a y with this name and a nested z with this other name". That is, in the first query there is no guaranteed relation between the y object with yname and the z object with zname: there is a guaranteed relation between those two objects, when the second query is used.

And I think that most people reading the first query would expect it to do what the second query does. That's where the problems with implicit joins arise.