Restoring an object tree from a federated query

Question

Suppose I have the following database model (not a real case, so please forgive me for the complete stupidity of this example):

I want to get the combined result from db so that I can create a tree of this object, starting with the Faculty element.

Thus, I would have the following case classes:

  case class StudentWithGrades(student: Student, grades: Seq[Grade]) case class GroupWithStudents(group: Group, students: Seq[Student]) case class FacultyWithGroups(faculty: Faculty, groups: Seq[Group]) 

and would like to restore the lines extracted from db so that in the end I just have Seq[FacultyWithGroups] .

Is there an easier way than this:

  db.run ( FacultyTable .join(GroupTable).on(_.id === _.facultyId) .join(StudentTable).on(_._2.id === _.groupId) .join(GradeTable).on(_._2.id === _.studentId) .result .map { _.groupBy { case (((faculty, group), student), _) => (faculty, group, student) } .map { case ((faculty, group, student), groupped) => (faculty, group, StudentWithGrades(student, groupped.map(_._2))) } .toSeq .groupBy { case (faculty, group, studentGrades) => (faculty, group) } .map { case ((faculty, group), groupped) => (faculty, GroupWithStudents(group, groupped.map(_._3))) } .toSeq .groupBy { case(faculty, group) => faculty } .map { case(faculty, groupped) => FacultyWithGroups(faculty, groupped.map(_.2)) } } ) 

Although this is actually related to Slick (restoring an object tree in a way similar to the classic ORM s), it seems to me that this is basically a big problem with clever collection handling than Slick .