i've got a typical many-to-many modeled as two one-to-many's. in this case, the sample shows the old favorite "students to courses" with an associative entity linking the two containing a grade attribute.

my issue has to do with an eager fetch on student by last name intended to gather the associative entities and corrosponding course entities in an eager fetch.

the eager fetch works well when the students being searched on have associations to courses,
but the fetch doesn't work well when the students don't have associations to courses.

i've tried "left fetch join" to account for this, but i'm getting zero hits.

so, i have a couple questions:

(1) is this a manifestation of:

http://opensource.atlassian.com/project ... se/HHH-554

(2) if not, does this fall under the "only fetch one collection rule" (altough it doesn't seem to)

(3) if not, can some one help with syntax for an eager left fetch join of this nature which will work both with and without associations present?

i'm trying the following and failing:

from Student s left join fetch s.courses left join fetch
s.courses.course where s.lastName = :lastName

Hibernate version: 3.0.5

Mapping documents:

Student.hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.kerz.test.model">

<class name="Student" table="STUDENT">
<id name="oid" column="OID">
<generator class="identity" />
</id>

<property name="lastName" column="LAST_NAME" />

<set name="courses" inverse="true"
cascade="all-delete-orphan">
<key column="STUDENT_OID" />
<one-to-many class="Student_Course" />
</set>
</class>

<query name="student.by.lastName">
from Student s where s.lastName like :lastName
</query>

<query name="student.by.lastName.eager">
from Student s left join fetch s.courses left join fetch
s.courses.course where s.lastName = :lastName
</query>

<query name="student.delete.all">delete from Student</query>
</hibernate-mapping>

Student_Course.hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.kerz.test.model">

<class name="Student_Course" table="STUDENT_X_COURSE">

<id name="oid" column="OID">
<generator class="identity" />
</id>

<property name="grade" column="GRADE" />

<many-to-one name="student" class="Student" column="STUDENT_OID" not-null="true" />

<many-to-one name="course" class="Course" column="COURSE_OID" not-null="true" />

</class>

</hibernate-mapping>

Course.hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.kerz.test.model">

<class
name="Course"
table="COURSE">

<id
name="oid"
column="OID">
<generator class="identity" />
</id>

<property
name="name"
column="NAME" />

<set
name="students"
inverse="true"
cascade="all-delete-orphan">
<key column="COURSE_OID" />
<one-to-many class="Student_Course" />
</set>

</class>

<query name="course.delete.all">delete from Course</query>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Transaction tx = session.beginTransaction();
Student s = new Student();
s.setLastName("smith");

Course c = new Course();
c.setName("math");
session.save(c);
Student_Course sc = new Student_Course();
sc.setGrade("B-");
sc.setCourse(c);
s.addCourse(sc);
session.save(s);

c = new Course();
c.setName("english");
session.save(c);
sc = new Student_Course();
sc.setGrade("C+");
sc.setCourse(c);
s.addCourse(sc);
session.save(s);

List list = (List) session.getNamedQuery("student.by.lastName.eager").setParameter("lastName",
"smith").list();

// weed out duplicates
Set set = new LinkedHashSet(list);

int idx = 0;
for (Iterator i = set.iterator(); i.hasNext();)
{
Student s2 = (Student) i.next();
System.out.println("student: [" + idx + "] = [" + s2 + "]");
idx++;
}
tx.commit();

Full stack trace of any exception that occurs:

Name and version of the database you are using: mysql 4.1.12 (win32)

The generated SQL (show_sql=true):

Hibernate: select student0_.OID as OID0_, courses1_.OID as OID1_, course3_.OID as OID2_, student0_.LAST_NAME as LAST2_0_0_, courses1_.GRADE as GRADE1_1_, courses1_.STUDENT_OID as STUDENT3_1_1_, courses1_.COURSE_OID as COURSE4_1_1_, courses1_.STUDENT_OID as STUDENT3_0__, courses1_.OID as OID0__, course3_.NAME as NAME2_2_ from STUDENT student0_ left outer join STUDENT_X_COURSE courses1_ on student0_.OID=courses1_.STUDENT_OID, STUDENT_X_COURSE courses2_ left outer join COURSE course3_ on courses2_.COURSE_OID=course3_.OID where student0_.OID=courses2_.STUDENT_OID and student0_.LAST_NAME=?
703 [main] DEBUG org.hibernate.type.StringType - binding 'jones' to parameter: 1

Debug level Hibernate log excerpt:

i believe i've solved the problem by using an alias for the first fetch join and referring to it in the subsequent fetch join:

from Student s left join fetch s.courses joinAlias left join fetch
joinAlias.course where s.lastName = :lastName

sometimes perseverence pays off! (and other times it enhances frustration) ;)