Hibernate version: 3.2
Hello, I'm wondering how to possibly write a better HQL join. I know what the SQL should look like, but in HQL I am having trouble getting this to generate that SQL, instead it generates a less efficient sub-select.
I have Java objects like
Code:
public class Collection {
Long collectionId;
List<MediaItem> item;
}
public class MediaItem {
Long itemId;
}
and the item list mapped as a one-to-many collection like this:
Code:
<list cascade="persist,merge,save-update"
lazy="true"
name="Item"
table="Collection_Item">
<cache usage="read-write"/>
<key>
<column name="Collection_Item_Hjid"/>
</key>
<list-index>
<column name="Collection_Item_Hjindex"/>
</list-index>
<one-to-many class="domain.MediaItem"/>
</list>
I want to find the Collection for a given MediaItem's primary key.
The HQL query I am using looks like this:
Code:
select Collection from domain.Collection Collection,
domain.MediaItem MediaItem
where MediaItem in elements(Collection.Item)
and MediaItem.id = ?
which generates a sub-select query like
Code:
SELECT COLLECTION0_.COLLECTIONID AS COLLECTI1_3_, ...
FROM COLLECTION COLLECTION0_, MEDIA_ITEM MEDIAITEM1_
WHERE (MEDIAITEM1_.ITEMID IN
(SELECT ITEM2_.ITEMID FROM MEDIA_ITEM ITEM2_
WHERE COLLECTION0_.COLLECTIONID=ITEM2_.COLLECTION_ITEM_HJID))
AND MEDIAITEM1_.ITEMID=2973
What I really want is like this SQL query:
Code:
select c.* from collection c, media_item m
where c.collectionid = m.collection_item_hjid
and m.itemid = ?
I can configure this in my Hibernate mapping doc, like
Code:
<sql-query name="CollectionForItemId">
<return alias="c" class="domain.Collection"/>
<![CDATA[select {c.*} from collection c, media_item m
where c.collectionid = m.collection_item_hjid
and m.itemid = ?]]>
</sql-query>
and it works great, but I was wondering if there is a way to do the same thing in HQL.
Thanks for any advice.
Login
FAQ
Search