Hibernate LEFT OUTER JOIN with DetachedCriteria

Question

I am trying to transform the following SQL into 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()

      )

I have the following code (commented out code are different iterations that I have tried):

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);
}

However I get two duplicate rows as a result of this and the SQL that hibernate uses is as following:

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<=?
                )
            )
    )

I have looked all over for a solution. Any help will be appreciated. I am also trying to avoid using HQL.

Kia

Answer 1

Getting duplicates from a complex hibernate criteria query is a common problem, and the easiest fix is to use a result transformer made just for this issue:

criteria.setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY );

Unfortunately, this doesn't interact well with scrolling and paging, but it may work in your case.

A better solution might be to replace some part of your restrictions with SubQuery.exists to get a sub-selection checking existence of matches instead of retrieval of all matches, but this can be painful to figure out.