Sleep mode subquery pending

Question

How to write a subquery in sleep mode that has several subqueries. eg

select * from project_dtls where project_id in (select project_id from project_users where user_id = (select user_id from user_dtls where email=' [e-mail] ')) 

I know that we can write through DetachedCriteria, but cannot find any example where I can use several subqueries.

Answer 1

Here is an example:

 DetachedCriteria exampleSubquery = DetachedCriteria.forClass(MyPersistedObject.class) .setProjection(Property.forName("id")) // plus any other criteria... ; Criteria criteria = getSession().createCriteria(ARelatedPersistedObject.class) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .add(Subqueries.propertyIn("myPersistedObjectId", exampleSubquery))); 

For multiple subqueries, you can use a logical operator of the type Restrictions.or ():

 DetachedCriteria anotherSubquery = DetachedCriteria.forClass(MyPersistedObject.class) .setProjection(Property.forName("id")) // plus any other criteria... ; Criteria criteria = getSession().createCriteria(ARelatedPersistedObject.class) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .add(Restrictions.or( Subqueries.propertyIn("myPersistedObjectId", exampleSubquery), Subqueries.propertyIn("myPersistedObjectId", anotherSubquery)));