JPQL Multiple Left Joins on Unrelated Classes

Question

I have three tables with columns (bad fake names):

STUDENT (student_id)
STUDENT_UNIQUE_INFO (student_unique_info_id, student_id)
STUDENT_OTHER_INFO (student_other_info_id, student_id)

I wish I could change the schema but I can't. Some students don't have unique_info and/or other_info.

My result set would be like:

STUDENT_ID | STUDENT_UNIQUE_INFO_FIELDS... | STUDENT_OTHER_INFO_FIELDS...

Where the fields could sometimes be null if the student didn't have any.

Problem is most examples would have STUDENT containing the IDs of the other tables, I don't have that option.

I tried something like (outputting to a new obj that takes in all 3 of the tables used to join this thing)

SELECT NEW path.to.outputObj(S,
       (SELECT SUI
       FROM path.to.SUI SUI
       WHERE S.studentId = SUI.studentId),
       (SELECT SOI
       FROM path.to.SOI SOI
       WHERE S.studentId = SOI.studentId))
       FROM STUDENT S

But it blew up because some students have multiple entries in student_other_info.

java.sql.SQLException: Subquery returns more than 1 row

I'm just a bit lost at how to even go about this.

Nicholas Hirras · Accepted Answer · 2019-01-17T19:11:06.510

1

You should map those other two tables as lists that could contain 0..n rows for each student.

class Student {
   @OneToMany(mappedBy = "student");
   List<StudentUniqueInfo> studentUniqueInfoList;

   @OneToMany(mappedBy = "student");
   List<StudentOtherInfo> studentOtherInfoList;
   ...
}

And then StudentUniqueInfo.java and StudentOtherInfo.java would each have a field

 @JoinColumn(name = "STUDENT_ID")
 private Student student;

edited Jan 17 '19 at 19:11

answered Jan 17 '19 at 19:06

Nicholas Hirras

2,592
2
21
28

This makes a lot of sense, and would be lovely instead of writing some insane JPQL. I'm going to try this out now. – canpan14 Jan 17 '19 at 19:09
You answer 100% works. I'm just curious if there is a way to get it to return a list of students, returning a different line item for each different other_info. So if one student had 5 other_info. I would get back 5 rows. I can easy code this out manually, but I figured I'd check. Thanks either way! – canpan14 Jan 17 '19 at 19:21
1

Never done that, but check out this post for some ideas about mapping a class across multiple tables. https://stackoverflow.com/questions/22668350/how-to-map-one-class-with-multiple-tables-in-hibernate-javax-persistance – Nicholas Hirras Jan 17 '19 at 19:34

JPQL Multiple Left Joins on Unrelated Classes

1 Answers1