I'm trying to do something like this, but using Criteria
instead of HQL
:
select user from User where user in (
select user from UserDomain where domain.id = "XXX"
)
User being an entity having a one-to-many
relationship to the join table UserDomain.
The point here is simply to find Users that are linked to a Domain
having id = "XXX".
This seems like it should be very simple... but I'm having no luck so far turning up any useful docs.
The subquery is very useful in cases, that you need to search the User, having one-to-many UserDomains. In that case, the WHERE UserId IN (subquery)
brings big advanatage: we are still working with a flat User
table/entity... so we can do the proper paging.
Here is the documentation 15.8. Detached queries and subqueries
The draft could be: subquery:
DetachedCriteria userSubquery = DetachedCriteria.forClass(UserDomain.class, "ud")
// Filter the Subquery
.add(Restrictions.eq(UserDomain.DOMAIN, domain))
// SELECT The User Id
.setProjection( Projections.property("ud.userId") );
And the main query:
Criteria query = session.createCriteria(User.class, "u")
.add( Subqueries.propertyIn("u.id", userSubquery) );
Now we have a query, which could be used for paging