Hello everyone. I am new to hibernate, but I have a prototype app running with
Hib 3.0.4, Oracle dialect so not a super n00b. I have spent lots of time looking into this and searching the archives, but no luck so far. I really appreciate this tool, and help from anyone who might have thoughts on this issue. :)
This post is kind of long with code samples, but the problem is very simple : The
GROUP BY generated SQL groups by the
ID column when I am retreiving the entire object, so it is trying to select columns that are not included in the
GROUP BY clause. I based by syntax exactly like an example in the manual. OK, details:
My problem is with an HQL query using GROUP BY.
Code:
select user
from User user
join user.VideoAssets asset
group by user
order by max(asset.createdDate) desc
the generated sql is:
Code:
select user0_.id as id, user0_.username as username1690_, user0_.password as password1690_
from MyUser user0_, Video_Asset videoasset1_, Asset videoasset1_1_
where user0_.id=videoasset1_.producer_id and videoasset1_.id=videoasset1_1_.id
group by user0_.id
order by max(videoasset1_1_.created_date) desc
And the error (obvious from the SQL) is:
Code:
ORA-00979: not a GROUP BY expression
The generated SQL groups by the
ID column, but the select is fetching the whole object.
One of the examples in the doc (
http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html#queryhql-grouping ) is very similar:
Code:
select cat
from eg.Cat cat
join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc
One difference I can see is that my class
VideoAsset is a
joined-subclass of the
Asset object. Is there a different syntax for that? I'll post by hibernate XML below. Thank you to anyone who has taken the time to read this far. I appreciate any thoughts... :)
Code:
<class name="User" table="MyUser">
<id column="id" name="id" type="long" unsaved-value="0">
<generator class="native" />
</id>
<property column="username" length="1500" name="username" type="string"/>
<property column="password" length="1500" name="password" type="string"/>
<set name="videoAssets" inverse="true" cascade="save-update">
<meta attribute="GENERIC">tv.current.base.model.VideoAsset</meta>
<key column="producer_id" />
<one-to-many class="VideoAsset" />
</set>
</class>
<class name="Asset" table="Asset">
<id column="id" name="id" type="long" unsaved-value="0">
<generator class="native" />
</id>
<many-to-one name="producer" column="producer_id" class="User" lazy="false" />
<property column="created_date" name="createdDate" type="timestamp"/>
<joined-subclass name="VideoAsset" table="Video_Asset">
<key column="id" />
<property column="thumbnail" length="1500" name="thumbnail" type="string"/>
<property column="location" length="1500" name="location" type="string"/>
<property column="duration" length="1500" name="duration" type="string"/>
<property column="width" length="1500" name="width" type="string"/>
<property column="height" length="1500" name="height" type="string"/>
</joined-subclass>
</class>
[/code]
Login
FAQ
Search
