[hibernate-users] Subquery issue

[Haswell, Joe]

Hello everyone,

I'm having a bit of an issue with executing a subquery.  I need to find all of the items whose IDs are not in a particular set.  If I obtain the set before-hand by executing the query that retrieves the set, then it works; if I try to execute the query that retrieves the set as a subquery, then it doesn't work (SQLException, column not found).

For example:

                              userGroupMappingsWithoutDefaultRoles =
                                             DetachedCriteria.forClass(UserToSecurityGroup.class)
                                                            .createAlias(
                                                                           "role",
                                                                           "securityRole"
                                                            ).add(
                                                                           Restrictions.not(
                                                                                          Restrictions.in(
                                                                                                         "securityRole.key",
                                                                                                         RoleLoader.DEFAULT_ROLE_KEYS
                                                                                          )
                                                                           )
                                                            ).setProjection(
                                                                           Projections.property("userGroup.id")
                                                            );

                              DetachedCriteria crit =
                                             DetachedCriteria.forClass(UserGroupMember.class)
                                                            .createAlias(
                                                                           "userGroup",
                                                                           "uGroup"
                                                            )
                                                            .add(
                                                                           Subqueries.propertyIn(
                                                                                          "uGroup.id",
                                                                                          userGroupMappingsWithoutDefaultRoles
                                                                           )
                                                            );
Fails

However:

                              List<PersistenceID> userGroupIds =
                                             userGroupMappingsWithoutDefaultRoles
                                                            .getExecutableCriteria(
                                                                           context.getSession()
                                                            ).list();

                              DetachedCriteria crit =
                                             DetachedCriteria.forClass(UserGroupMember.class)
                                                            .createAlias(
                                                                           "userGroup",
                                                                           "uGroup"
                                                            )
                                                            .add(
                                                                           Property.forName("uGroup.id").in(userGroupIds)
                                                            );

Works.  There's gotta be something simple that I'm doing wrong here.  Any help would be appreciated!

Joe H.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-users/attachments/20101111/af2fc608/attachment-0001.html