How use hibernate criteria for a left outer join without direct link into the two tables

Question

I'm trying to translate a SQL quest into Hibernate criteria.

My quest is working in SQL :

    select * from objective 
    left outer join conditionstate 
      on objective.conditionid = conditionstate.conditionid 
         and conditionstate.personid = XXXX 
    where objective.toto_id = YYYYY

The objective is not directly mapped to the condition_state, but into a condition.

objective --> condition <-- condition_state

I've tested something like :

    final DetachedCriteria criteriaObjective = 
       DetachedCriteria.forClass(Objective.class);
    criteriaObjective.createAlias("conditionState", "conditionState", Criteria.LEFT_JOIN);

without success..

Answer 1

It's hard to suggest something without seeing your actual mappings. Going by your explanation and assuming that both condition and conditionState are mapped as many-to-one, you'd write something like:

final DetachedCriteria criteriaObjective = 
  DetachedCriteria.forClass(Objective.class);
criteriaObjective
  .createCriteria("condition", Criteria.LEFT_JOIN)
  .createCriteria("conditionState", Criteria.LEFT_JOIN)
  .add(Restrictions.eq("personid", "XXXX") );
criteriaObjective.add(Restrictions.eqProperty("toto_id", "YYYY") );

Note that the above is NOT equivalent to the SQL query you've provided because "personid" condition will be generated as part of "WHERE" clause. As far as I know it's impossible to do a left join with condition using Criteria API - you may need to use HQL instead which provides with keyword for that exact purpose:

select o from objective as o
  left join o.condition as c
  left join c.conditionState as cs
  with cs.person_id = 'XXXX'
  and o.toto_id = 'YYYY'