More condition on hibernate left join clause

Question

How to achieve subquery in hibernate criteria left join?

I tried in this way,

DetachedCriteria subquery = DetachedCriteria.forClass( Comment.class, "comment").add(Restrictions.eq("comment.divisionId", divisionId));

    subquery.setProjection(Projections
            .groupProperty("comment.commentId"));

    Session session = getPersManager().getCurrentSession();
    Criteria criteria = session.createCriteria(PropertyType.class, "type")
            .createAlias("type.comments", "comment",CriteriaSpecification.LEFT_JOIN)
            .add(
            Subqueries
                    .propertyIn("comment.commentId", subquery))
            .setResultTransformer(
                    CriteriaSpecification.DISTINCT_ROOT_ENTITY);
    List lst = criteria.list();

but it returns,

select this_.PRTY_TYP_ID as PRTY1_4_1_, 
this_.PRTY_TYP_NAME as PRTY2_4_1_, this_.ACTV as ACTV4_1_, 
this_.CRTD_USER_ID as CRTD4_4_1_, this_.CRTD_DATE as CRTD5_4_1_, 
this_.UPDT_USER_ID as UPDT6_4_1_, this_.UPDT_DATE as UPDT7_4_1_, 
comment1_.PRTY_TYP_ID as PRTY4_3_, comment1_.CMNT_ID as CMNT1_3_, 
comment1_.CMNT_ID as CMNT1_6_0_, comment1_.CMNT_TXT as CMNT2_6_0_, 
comment1_.ACTV as ACTV6_0_, comment1_.PRTY_TYP_ID as PRTY4_6_0_, 
comment1_.DVSN_ID as DVSN5_6_0_, comment1_.CRTD_USER_ID as CRTD6_6_0_, 
comment1_.CRTD_DATE as CRTD7_6_0_, comment1_.UPDT_USER_ID as UPDT8_6_0_, 
comment1_.UPDT_DATE as UPDT9_6_0_ 
from T_LOOKUP_PROPERTY_TYPE this_ 
left outer join T_LOOKUP_COMMENT  comment1_ 
on this_.PRTY_TYP_ID=comment1_.PRTY_TYP_ID 
where comment1_.CMNT_ID in (select comment_.CMNT_ID as y0_ 
from T_LOOKUP_COMMENT  comment_ 
where comment_.DVSN_ID=? group by comment_.CMNT_ID)

How to achieve the following query?via HQL or criteria

T_LOOKUP_PROPERTY_TYPE - PropertyType
T_LOOKUP_COMMENT  - Comment


select * from dbo.T_LOOKUP_PROPERTY_TYPE as ptype
left join (select * from dbo.T_LOOKUP_COMMENT where DVSN_ID = 9) as comm
on ptype.PRTY_TYP_ID = comm.PRTY_TYP_ID

help me..

Answer 1

This is achieved using Filter concept. Thanks.