Jooq query nested object with different name than table

I have a problem really similar to the one described https://stackoverflow.com/questions/71090417/jooq-query-nested-object. To summarize I have this pojo:

public class CourseComposite {
  private String courseName;
  private Teacher teacher;
  private List<Student> students;
  ...
}

and I'm using the Jooq DefaultRecordMapper to map the record to the pojo, having the following query:

    ctx().select(
            COURSE.NAME.as("courseName"), 
            TEACHERS, 
            multiset(selectFrom(STUDENT).where(STUDENT.ID_COURSE.eq(COURSE.ID))).as("students")
          )
          .from(COURSE).innerJoin(TEACHERS).on(COURSE.ID.eq(TEACHER.ID_COURSE))
          .where(...)
          .fetchInto(CourseComposite.class);

However teacher is not mapped (the teacher field is null). I think it's because my postgresql table is called teachers and not teacher (this is the only difference with the stack overflow question mentioned above).

I tried to write:

ctx().select(COURSE.NAME.as("courseName"), TEACHERS.as("teacher"), multiset(...))

however in this case I get an exception:

> missing FROM-clause entry for table "teacher"

Is there a way to tell jooq that TEACHERS should be binded to the Teacher teacher object?

This is "expected", though admittedly, a bit weird, because you're really aliasing a Table, not a SelectField (the API is the same). The fact that you're aliasing TEACHERS.as("teacher") only in the SELECT clause for that particular purpose is not known to jOOQ. You might as well use the alias like any other table alias. Think about it this way:

Teachers teacher = TEACHERS.as("teacher");

Now, you'd be using it as follows:

ctx().select(..., teacher, ...)

jOOQ can't just assume you meant for the alias to apply only in the SELECT clause. What if you added it to your FROM clause as well, as indicated by PostgreSQL's error message? I.e.

innerJoin(teacher)

Because then, it would make sense to project the teacher alias reference, not the TEACHERS table reference. You might argue that jOOQ could detect what's The Right Thing™, because you clearly didn't include teacher in the FROM clause, but there can be many edge cases, especially when you have both (via self-join).

So, the solution is to just use teacher in the FROM clause, instead of TEACHERS.

Alternatives

You can obviously work around the problem by moving the mapping into the query, at least in parts, using an ad-hoc converter. You could write:

TEACHERS.convertFrom(r -> r.into(Teacher.class))

In that case, the mapping from the TeachersRecord to your Teacher class would happen before trying to map the nested record into the parent object. It's also worth thinking about removing the reflection based DefaultRecordMapper entirely, and turn your objects into records:

public record CourseComposite(
  String courseName, 
  Teacher teacher, 
  List<Student> students
) {}

Now, you can use fetchInto(Records.mapping(CourseComposite::new)) at the end of your query, all type safe!