How can I left join a subquery in hibernate with criteria?

Question

I want to be able to left join a subquery in hibernate with criteria.

Here is sample query:

Select * From Order o
Left Join (Select * From Product p Where p.accountId = 3) p
On p.id = o.productId
Where (p.category is not null and p.category = 'clothes') or 
(p.category is null and o.category = 'clothes')

Note this is a sample query, mine is more complex and I need to be able to left join a subquery.

How could you generate this query with the hibernate criteria syntax?

I would like to do something similar to this:

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(Order.class);

DetachedCriteria productCriteria = DetachedCriteria.forClass(Product.class);
productCriteria.add(Restrictions.eq("accountId", 3));

criteria.createAlias("productCriteria", "p", JoinType.LEFT_OUTER_JOIN, productCriteria);

criteria.add(Restrictions.or(
        Restrictions.and(Restrictions.isNotNull("p.category"), Restrictions.eq("p.category", "clothes")),
   Restrictions.and(Restrictions.isNull("p.category"), Restrictions.eq("category", "clothes"))
));

List list = criteria.list();

Edit

In my real problem the subquery consists of two tables.

Answer 1

You can use createAlias API of Criteria class and specify join type. Refer this documentation for more details.

    Criteria createAlias(String associationPath,
                 String alias,
                 int joinType)
                 throws HibernateException
Join an association using the specified join-type, assigning an alias to the joined association.
The joinType is expected to be one of CriteriaSpecification.INNER_JOIN (the default), CriteriaSpecification.FULL_JOIN, or CriteriaSpecification.LEFT_JOIN.