Hibernate JOINTURE EXTERNE GAUCHE avec DetachedCriteria

Je suis en train de transformer le SQL suivant dans Hibernate DetachedCriteria:

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>= NOW() 
                    AND assignment2_.startDate<= 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 = <id>
            AND this_.endDate>= NOW()
            AND this_.startDate<= NOW()

      )

J'ai le code suivant (en commentaire de code sont différentes itérations que j'ai essayé):

public Collection<Course> 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);
}

Mais j'ai deux lignes en double à la suite de ce et le SQL que hibernate utilise est le suivant:

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>=? 
            and this_.startDate<=? 
            and (
                assignment2_.availabilityType=? 
                or (
                    assignment2_.availabilityType=? 
                    and assignment2_.endDate>=? 
                    and assignment2_.startDate<=?
                )
            )
    )

J'ai cherché partout pour trouver une solution. Toute aide sera appréciée. Je suis aussi en train d'essayer d'éviter d'utiliser les requêtes HQL.

Kia

OriginalL'auteur Kianosh | 2011-01-13