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.