How to join 3 tables and iterate results using jooq?

I have COURSE, STUDENT, SCHEDULE tables.

table course(id, name, ....), 
table student(id, name, ...), 
table schedule(id, c_id, s_id).

Now I want to left join schedule table with course and student table.

Question (1):

What's the best way to do join these 3 tables in jooq? I assume it's like:

TableLike<?> firstjoin = sql
    .select()
    .from(Tables.SCHEUDLE)
    .leftOuterJoin(Tables.COURSE)
    .on(Tables.SCHEDULE.CID.eq(Tables.COURSE.ID))
    .asTable();

Result<?> result = sql
    .select()
    .from(firstjoin)
    .leftOuterJoin(Tables.STUDENT)
    .on(Tables.SCHEDULE.SID.eq(Tables.STUDENT.ID))
    .fetch();

Question (2):

When I get the result, what's the best way to split results into Student objects and Course objects? I mean since the type is Result?, is there any way we can mapping result into student, course entities instead of tediously doing something like this:

for(Record r: result){
   Student s = new Student(r.filed(), r.filed()...);
   Course c = new Course(r.filed(), r.filed()....)
}

Answer 1

What's the best way to do join these 3 tables in jooq? I assume it's like [...]

While your query is correct, I wouldn't join like you did. Your approach creates a derived table, which

1. Adds complexity to the SQL statement with no value, e.g. when maintaining the statement
2. Prevents optimisation in some databases that poorly handle derived tables

Instead, just join both tables in a single statement:

Result<?> result = sql
    .select()
    .from(SCHEUDLE)
    .leftOuterJoin(COURSE)
    .on(SCHEDULE.CID.eq(COURSE.ID))
    .leftOuterJoin(STUDENT)
    .on(SCHEDULE.SID.eq(STUDENT.ID))
    .fetch();

Answer 2

You can use one of the various Record.into() methods, such as Record.into(Table)

for (Record r : result) {
    StudentRecord s = r.into(STUDENT);
    CourseRecord c = r.into(COURSE);
}

A general remark on alternative approaches

Since jOOQ 3.15, jOOQ allows for nesting collections using its standard SQL MULTISET value constructor operator, which can be emulated using SQL/JSON or SQL/XML on a variety of RDBMS. This approach would probably better solve the underlying problem you have.

sql.select(
        SCHEDULE,
        multiset(
            select(
                SCHEDULE.course(),
                multiset(
                    selectFrom(SCHEDULE.course().student())
                )
            )
            .from(SCHEDULE.course())
        )
    )
   .from(SCHEDULE)
   .fetch();

The above approach also uses:

• jOOQ 3.17's possibility of nesting records via table expressions in the projection (select(SCHEDULE) where SCHEDULE is a Table)
• jOOQ 3.19's implicit path correlation to simplify correlating subqueries (including MULTISET correlated subqueries).

Furthermore, in order to map jOOQ records to your own DTO's you might want to use jOOQ 3.15's ad-hoc conversion possibilities