title: Hibernate LEFT OUTER JOIN with DetachedCriteria
body: <p>I am trying to transform the following SQL into Hibernate DetachedCriteria:</p>
  
  <pre><code>SELECT
      students0_.courseId AS courseId2_1_,
      students0_.studentId AS studentId1_,
      student1_.id AS id3_0_,
      student1_.href AS href3_0_,
      student1_.created AS created3_0_,
      student1_.email AS email3_0_,
      student1_.enabled AS enabled3_0_,
      student1_.firstName AS firstName3_0_,
      student1_.lastAccessed AS lastAcce8_3_0_,
      student1_.lastName AS lastName3_0_,
      student1_.password AS password3_0_,
      student1_.role AS role3_0_,
      student1_.username AS username3_0_ 
  FROM
      course_students students0_ 
  INNER JOIN
      users student1_ 
          ON students0_.studentId=student1_.id 
  WHERE
      students0_.courseId IN (
          SELECT
              this_.id 
          FROM
              courses this_ 
          LEFT OUTER JOIN
              assignments assignment2_ 
                  ON this_.id=assignment2_.courseId 
                  AND (
                  assignment2_.availabilityType='Available' 
                  OR (
                      assignment2_.availabilityType='Range'
                      AND assignment2_.endDate&gt;= NOW() 
                      AND assignment2_.startDate&lt;= NOW()
                  )
              )
          LEFT OUTER JOIN
              courses course5_ 
                  ON assignment2_.courseId=course5_.id 
          INNER JOIN
              course_students students6_ 
                  ON this_.id=students6_.courseId 
          LEFT OUTER JOIN
              users student1_ 
                  ON students6_.studentId=student1_.id 
          WHERE
              student1_.id = &lt;id&gt;
              AND this_.endDate&gt;= NOW()
              AND this_.startDate&lt;= NOW()
  
        )
  </code></pre>
  
  <p>I have the following code (commented out code are different iterations that I have tried):</p>
  
  <pre><code>public Collection&lt;Course&gt; findCoursesByStudent(Student student) {
      Calendar currTime = Calendar.getInstance();
  //        DetachedCriteria subCriteria = DetachedCriteria.forClass(Assignment.class, "assignments");
      //subCriteria.createCriteria("assignments", Criteria.FULL_JOIN);
      //subCriteria.createAlias("assignments", "assignments");
      Disjunction disjunction = Restrictions.disjunction();
      disjunction.add(Restrictions.eq("assignments.availabilityType", AvailabilityType.Available));
  
      Conjunction conjunction = Restrictions.conjunction();
      conjunction.add(Restrictions.eq("assignments.availabilityType", AvailabilityType.Range));
      conjunction.add(Restrictions.ge("assignments.endDate", currTime)).add(Restrictions.le("assignments.startDate", currTime));
  
      disjunction.add(conjunction);
  
  //        subCriteria.add(disjunction);
  
      DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
      criteria.createCriteria("students", "student").add(Restrictions.idEq(student.getId()));
      criteria.add(Restrictions.ge("endDate", currTime)).add(Restrictions.le("startDate", currTime));
      criteria.createAlias("assignments", "assignments", Criteria.LEFT_JOIN);
      criteria.setFetchMode("assignments", FetchMode.JOIN).add(disjunction);
      //criteria.createCriteria("assignments", "assignments", Criteria.LEFT_JOIN).add(disjunction);
      return findByDetachedCriteria(criteria);
  }
  </code></pre>
  
  <p>However I get two duplicate rows as a result of this and the SQL that hibernate uses is as following:</p>
  
  <pre><code>select
      students0_.courseId as courseId2_1_,
      students0_.studentId as studentId1_,
      student1_.id as id3_0_,
      student1_.href as href3_0_,
      student1_.created as created3_0_,
      student1_.email as email3_0_,
      student1_.enabled as enabled3_0_,
      student1_.firstName as firstName3_0_,
      student1_.lastAccessed as lastAcce8_3_0_,
      student1_.lastName as lastName3_0_,
      student1_.password as password3_0_,
      student1_.role as role3_0_,
      student1_.username as username3_0_ 
  from
      course_students students0_ 
  inner join
      users student1_ 
          on students0_.studentId=student1_.id 
  where
      students0_.courseId in (
          select
              this_.id 
          from
              courses this_ 
          left outer join
              assignments assignment2_ 
                  on this_.id=assignment2_.courseId 
          left outer join
              courses course5_ 
                  on assignment2_.courseId=course5_.id 
          inner join
              course_students students6_ 
                  on this_.id=students6_.courseId 
          left outer join
              users student1_ 
                  on students6_.studentId=student1_.id 
          where
              student1_.id = ? 
              and this_.endDate&gt;=? 
              and this_.startDate&lt;=? 
              and (
                  assignment2_.availabilityType=? 
                  or (
                      assignment2_.availabilityType=? 
                      and assignment2_.endDate&gt;=? 
                      and assignment2_.startDate&lt;=?
                  )
              )
      )
  </code></pre>
  
  <p>I have looked all over for a solution. Any help will be appreciated. I am also trying to avoid using HQL.</p>
  
  <p>Kia</p>