There are several ways to phrase this question, and I think I know the answer, but here goes anyway.
I have a two-way many-to-many mapping:
Code:
<hibernate-mapping>
<class name="com.nimblefish.core.domain.asset.Asset">
<id name="id" type="long" unsaved-value="null" >
<generator class="native"/>
</id>
<property name="name" type="string"/>
<!-- assets have multiple versions (and versions can be in multiple assets) -->
<list name="versions" table="asset_to_version" lazy="true" cascade="all-delete-orphan">
<key column="asset_id"/>
<index column="asset_index"/>
<many-to-many column="version_id" class="com.nimblefish.core.domain.asset.Version"/>
</list>
</class>
<!-- A Version is an immutable pointer to a particular item in the version store, or else
an immutable pointer to multiple child Versions. Versions are transitively immutable
once constructed. -->
<class name="com.nimblefish.core.domain.asset.Version">
<id name="id" type="long" unsaved-value="null" >
<generator class="native"/>
</id>
<property name="name" type="string"/>
<!-- assets have a version history, but versions are NOT a strict parent-child relationship since
a single version can be in the version history of multiple assets. -->
<set name="assets" table="asset_to_version" lazy="true" inverse="true">
<key column="version_id"/>
<many-to-many column="asset_id" class="com.nimblefish.core.domain.asset.Asset"/>
</set>
</class>
</hibernate-mapping>
Like I said, a two-way many-to-many.
Now. Let's say we have the following contents of our tables:
Code:
Asset
asset_id name
1 "foo"
2 "bar"
Version
version_id name
1 "foo"
2 "bar"
asset_to_version
asset_id version_id
1 1
1 2
2 1
What I want is to be able to clean up unreferenced Versions when there are no existing Assets that reference them. So let's say I delete asset 1, which cleans up the references from asset 1 to versions 1 and 2. Now version 2 is unreferenced by any Asset.
I want to write an HQL query which selects for all (and only) the Versions that are unreferenced by an Asset.
Some wild stabs in the dark at how this might look:
Code:
from Version v where count(v.assets) = 0
But this doesn't work because you can't use collections in a where clause, let alone collection functions. When trying it in hibern8ide, I get
Code:
net.sf.hibernate.QueryException: unindexed collection before []: version0_.assets
In SQL, one could write:
Code:
select * from Version where not exists (select * from asset_to_version where asset_to_version.version_id = version.version_id)
But Hibernate doesn't support subselects.
Or:
Code:
select * from Version left outer join asset_to_version on version.version_id = asset_to_version.version_id where asset_to_version.version_id = null
But Hibernate doesn't support arbitrary outer joins (according to
http://forum.hibernate.org/viewtopic.php?p=2199096#2199096 anyway).
So. Can anyone suggest any crafty way to do this in HQL? Or am I down in the world of pass-through SQL? (And can you experiment with pass-through SQL in Hibern8IDE?)
Cheers!
Rob
Login
FAQ
Search
