How to use sleep criteria for a left outer join without a direct link to two tables

Question

I am trying to translate an SQL quest into Hibernate criteria.

My quest works in SQL:

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

The object is not directly mapped to the condition_state, but in state.

objective --> condition <-- condition_state

I tested something like:

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

without success ..

Answer 1

It is difficult to offer something without seeing your actual comparisons. Based on your explanation and assuming that both conditions and conditionState are displayed as many-to-one, you should 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") );

, SQL, , "personid" WHERE. , API - HQL, :

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'