I am working on a similiar problem, with a report query where I have "unmapped" (unidirectional mappings) classes. Here's the relevant parts of my mapping files:
<many-to-one name="collection" class="com.Collection" >Collection.hbm.xml:
<column name="collectionid" sql-type="int" not-null="true" />
<set name="items" table="collectionitem" lazy="false">Item.hbm.xml:
<key column="collectionid" />
<many-to-many class="com.Item" column="itemid" />
<set name="collections" table="collectionitem" lazy="false" inverse="true">Image.hbm.xml:
<key column="itemid" />
<many-to-many class="com.Collection" column="collectionid" />
<many-to-one name="item" class="com.Item">
<column name="itemid" sql-type="int"/>
The mapping in Image.hbm.xml is unidirectional, there is no reference to Images from an Item.
The mapping in Category.hbm.xml is also unidirectional, there is no reference to the Category from a Collection
What I want to know is how many images exists per Category (including those with zero images). What I have so far is:
String query = "select cat.name, count(image) " +
"from Category cat, Image image " +
"join cat.collection.items item " +
"where item = image.item " +
"group by cat.name";
This works, except that no row/object is returned when there are 0 images under a category (which is to expect according to the way a where clause works (limiting the results)). I have tried a dozen variations using only joins, but I just can't get it right. Since some of the mappings are unidirectional it makes matters worse. As etwcn
pointed out, Hibernate can't figure out itself what fields to use, and Hibernate (HQL) doesn't support the the ON
I believe I could worked it out if they were bi-directional (ie. the classes were fully mapped), but that option is a last resort, I don't want Item to know about it images (analogous to previous post: B to be associated with A)...
Could anyone please help shed some light on this issue and/or suggest suitable modifications to the query?