Hibernate version: 3.2.3

I have User object that holds a set of Groups. I'm trying to create a criteria query that will provide me with a list of Groups the that any particular user doesn't belong to at the moment, so not in the set of groups on user.

First I created a DetachedCriteria to be used as a subquery.

DetachedCriteria userGroups = DetachedCriteria.forClass( User.class )
.add( Restrictions.eq( "id", user.getID() ) )
.add( Restrictions.eq( "destination", testDest ) )
.createAlias( "groups", "group" )
.setProjection( Projections.property( "group.id" ) );

This works as I would expect and returns a list of group ids that currently the user belongs to. Which I verify by attaching the DetachedCriteria to the session and running it.

Criteria userGroupsCrit = userGroups.getExecutableCriteria( session );
List userGroupList = userGroupsCrit.list();

The sql generated from this is.

select group1_.GROUP_ID as y0_ from IME_USER this_ inner join GROUP_MEMBERSHIP groups3_ on this_.USER_ID=groups3_.USER_ID inner join IME_GROUP group1_ on groups3_.GROUP_ID=group1_.GROUP_ID where this_.USER_TYPE='user' and this_.DESTINATION_ID=? and this_.USER_ID=?

Second I create a Group Criteria.

Criteria groupCrit = session
.createCriteria( AbstractGroup.class, "group" )
.add( Restrictions.eq( "destination", testDest ) )
.add( Subqueries.notExists( userGroups ) );

If the sql generated from the above were used in this criteria I would be golden but alas it becomes garbled.

The sql generated from the groupCrit is:

select this_.GROUP_ID as GROUP1_75_0_, this_.DESTINATION_ID as DESTINAT3_75_0_, this_.NAME as NAME75_0_, this_.CREATION_DATE as CREATION5_75_0_, this_.ATTRIBUTE_1 as ATTRIBUTE6_75_0_, this_.ATTRIBUTE_2 as ATTRIBUTE7_75_0_, this_.ATTRIBUTE_3 as ATTRIBUTE8_75_0_, this_.ATTRIBUTE_4 as ATTRIBUTE9_75_0_, this_.ATTRIBUTE_5 as ATTRIBUTE10_75_0_, this_.ATTRIBUTE_6 as ATTRIBUTE11_75_0_, this_.ATTRIBUTE_7 as ATTRIBUTE12_75_0_, this_.ATTRIBUTE_8 as ATTRIBUTE13_75_0_, this_.ATTRIBUTE_9 as ATTRIBUTE14_75_0_, this_.ATTRIBUTE_10 as ATTRIBUTE15_75_0_, this_.GROUP_TYPE as GROUP2_75_0_ from IME_GROUP this_ where this_.DESTINATION_ID=? and this_.GROUP_ID not in (select group1_.GROUP_ID as y0_ from IME_USER this0__ where this0__.DESTINATION_ID=? and this0__.USER_ID=?)

In between the parens at the end of the statement is where the DetachedCriteria generates its contribution but it has become senile, forgetting that its supposed to look for group ids attached to the user in a set not directly as a property.

Is what I'm trying to do possible? Is there some tweaks I'm missing? Or is there a much better way using Criteria to do this? I need it to be Criteria as we will be using this same structure throughout our system and we need to be able to paginate and provide search capabilities to each of these queries.

Thanks for any help.

You may have run into a bug, HHH-952. I have implemented the patch (file #5) in 3.2.2 and it works fine. Please vote for this bug and hopefully it can be incorporated in the next release.

Thank you Ananasi,

I downloaded the patch and this resolved my issue perfectly. Much obliged to the quick reply.