sql - Hibernate Create Criteria to join the same table twice - tried 2 approach with 2 difference error

Question

I would like to create create criteria for the following native sql.

Unfortunately, I hit error of duplicate associate path when use createCriteria twice. When I try to use Restrictions.sqlRestriction. It unable provide the SQL that I want.

Try 1: Create Criteria - duplicate associate path

 Criteria criteria = getSession().createCriteria( Company.class );
                 criteria.createAlias( "customerCategories", "c1" );
                 criteria.add( Restrictions.in( "c1.customerCategory.customerCategoryId",
                         company.getBaseCustomerCategoryId() ) );
                 criteria.createAlias( "customerCategories", "c2" );
                 criteria.add( Restrictions.in( "c2.customerCategory.customerCategoryId",
                         company.getPromoCustomerCategoryId() ) );

Try 2: Create SQL Restriction - ORA-00920: invalid relational operator because of "where"

  Criteria criteria = getSession().createCriteria( Company.class );
                 criteria.add( Restrictions.sqlRestriction(
                         "INNER JOIN Company_Customercategory a on {alias}.companyId = a.companyId and a.CUSTOMERCATEGORYID = ?",
                         company.getBaseCustomerCategoryId(), LongType.INSTANCE ) );
                 criteria.add( Restrictions.sqlRestriction( 
                         "1=1 INNER JOIN Company_Customercategory b on {alias}.companyId = b.companyId
 and b.CUSTOMERCATEGORYID = ?", 
                         company.getPromoCustomerCategoryId(), LongType.INSTANCE) );

Wrong Result

select this_.* from Companies this_ where 
  INNER JOIN Company_Customercategory a 
  on this_.companyId = a.companyId 
  and a.CUSTOMERCATEGORYID = 1
  and 1=1 INNER JOIN Company_Customercategory b 
  on this_.companyId = b.companyId 
  and b.CUSTOMERCATEGORYID = 6

Expected SQL

select * from companies c
  inner join Company_Customercategory a
  on c.companyId = a.companyId
  and a.CUSTOMERCATEGORYID = 1
  inner JOIN Company_Customercategory b
  on a.companyId = b.companyId
  and b.CUSTOMERCATEGORYID = 6

Appreciate your help. Thanks.

Answer 1

There is an old Hibernate bug HHH-879 on the problem of org.hibernate.QueryException: duplicate association path opened 2005 and still open...

Other issue is closed without solution HHH-7882

So the option 1) is rather not suitable.

But in the comments of the above bug an usefull workaround is mentioned using exists

So use twice sqlRestriction with exists and a correlated subquery filtering the propper category. You will get only companies connected to both categories.

crit.add( Restrictions.sqlRestriction( 
  "exists (select null from Company_Customercategory a where {alias}.company_Id = a.company_Id and a.CUSTOMERCATEGORYID = ?)",
  1, IntegerType.INSTANCE ) );
crit.add( Restrictions.sqlRestriction( 
  "exists (select null from Company_Customercategory a where {alias}.company_Id = a.company_Id and a.CUSTOMERCATEGORYID = ?)",
  6, IntegerType.INSTANCE ) );

This leads to the following query which provides the correct result

select this_.COMPANY_ID as COMPANY_ID1_2_0_, this_.COMPANY_NAME as COMPANY_NAME2_2_0_ 
from COMPANIES this_ 
where exists (select null from Company_Customercategory a 
              where this_.company_Id = a.company_Id and a.CUSTOMERCATEGORYID =  ?) and 
      exists (select null from Company_Customercategory a 
              where this_.company_Id = a.company_Id and a.CUSTOMERCATEGORYID = ?)