Hello. Using Hibernate 2.0.3. This follows on from topic
http://forum.hibernate.org/viewtopic.php?t=925550, and from the FAQ on ordering by the size of a collection
http://www.hibernate.org/118.html#A6.
The problem concerns the group by clause.
The examples below assume a structure where country has a one-to-many parent-to-child relationship with author, and author has a one-to-many parent-to-child relationship with book.
Problem 1 and solution 1 are as per the previous topic. Problem 2 and solution 2 are apparently new on this forum.
Problem 1. Select all authors having one or more books.
HQL:
Code:
SELECT author FROM tests.Author author
JOIN author.books book
GROUP BY author
HAVING count(book)>0
Generated SQL:
Code:
select author0_.authorId as authorId, author0_.name as name, author0_.countryId as countryId from Author author0_
inner join Book books1_ on author0_.authorId=books1_.authorId
group by author0_.authorId
having (count(books1_.bookId)>0 )
Error:
Could not execute query: Column 'author0_.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clauseSolution 1. Explicitly group by the author's attributes.
HQL:
Code:
SELECT author FROM tests.Author author
JOIN author.books book
GROUP BY author, author.country, author.name
HAVING count(book)>0
Generated SQL:
Code:
select author0_.authorId as authorId, author0_.name as name, author0_.countryId as countryId from Author author0_
inner join Book books1_ on author0_.authorId=books1_.authorId
group by author0_.authorId , author0_.countryId , author0_.name
having (count(books1_.bookId)>0 )
Error: none.
Problem 2: now we add an inner join from Author to Country (e.g. select all authors from countries starting with S having one or more books).
HQL:
Code:
SELECT author FROM tests.Author author
JOIN author.books book
JOIN author.country country
WHERE country.name like ?
GROUP BY author, author.country, author.name
HAVING count(book)>0
Generated SQL:
Code:
select author0_.authorId as authorId, author0_.name as name, author0_.countryId as countryId from Author author0_
inner join Book books1_ on author0_.authorId=books1_.authorId
inner join Country country2_ on author0_.countryId=country2_.countryId where (country2_.name like ? )
group by author0_.authorId , country2_.countryId , author0_.name
having (count(books1_.bookId)>0 )
Error:
Could not execute query: Column 'author0_.countryId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Note that adding country as an inner join caused the explicit reference to
author.country to be replaced by country2_.countryId, rather than author_0.countryId, thus causing the failure.
Solution 2 (clumsy) - replace the inner join by an exists clause
HQL:
Code:
SELECT author FROM tests.Author author
JOIN author.books book
WHERE EXISTS (SELECT country FROM tests.Country country
WHERE author.country=country
AND country.name like ?)
GROUP BY author, author.country, author.name
HAVING count(book)>0
Generated SQL:
Code:
select author0_.authorId as authorId, author0_.name as name, author0_.countryId as countryId from Author author0_
inner join Book books1_ on author0_.authorId=books1_.authorId
where (EXISTS(select country2_.countryId from Country country2_ where (author0_.countryId=country2_.countryId )AND(country2_.name like ? )))
group by author0_.authorId , author0_.countryId , author0_.name
having
(count(books1_.bookId)>0 )
No errors, but perhaps there is a more elegant solution to this?
Best regards,
Assaf
Login
FAQ
Search