Databases
StackOverflow2013
Postsdbo
POHibernate LEFT OUTER JOIN with DetachedCriteria
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate LEFT OUTER JOIN with DetachedCriteria
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload