How to use multiset to get associated list of records with JOOQ?

I am trying to get a record (engagement note), along with a list of associated records (attendees). I want to store them all as records, but cannot seem to get it to work. All examples I've seen online of multiset just use a few fields rather than the entire record. This is also in Kotlin if that makes a difference.

See below for what I'm attempting to do, it does not work.

  context.select(
        ENGAGEMENT_NOTE.asterisk(),
        multiset(
            select()
                .from(ENGAGEMENT_NOTE_ATTENDANCE)
                .join(ENGAGEMENT_NOTE_ATTENDEE).on(ENGAGEMENT_NOTE_ATTENDANCE.ATTENDEE_ID.eq(ENGAGEMENT_NOTE.ID))
                .where(ENGAGEMENT_NOTE_ATTENDANCE.ENGAGEMENT_NOTE_ID.eq(ENGAGEMENT_NOTE.ID))
        ).convertFrom { it.into(ENGAGEMENT_NOTE_ATTENDEE) }
            .`as`("attendees")
    )
        .from(ENGAGEMENT_NOTE)
        .where(ENGAGEMENT_NOTE.ID.eq(noteId.value))
        .fetchOne()
        ?.map {
            EngagementNoteView(
                it.into(ENGAGEMENT_NOTE),
                it.getValue("attendees") as List<EngagementNoteAttendeeRecord> // This is definitely wrong.
            )
        }

Use selectFrom(Table<R>) to return a Result<R>, instead of select().from(table).

jOOQ 3.19 solution

The upcoming jOOQ 3.19 to-many path expressions along with implicit path correlations will make this super simple:

context
    .select(

        // You can use a Table<R> as a SelectField<R>
        ENGAGEMENT_NOTE,
        multiset(

            // jOOQ 3.19 many-to-many implicit join path correlation
            selectFrom(ENGAGEMENT_NOTE.engagementNoteAttendee())
        ).`as`("attendees")
    .from(ENGAGEMENT_NOTE)
    .where(ENGAGEMENT_NOTE.ID.eq(noteId.value))
    .fetchOne()

Note, the above also uses a Table<R> as a SelectField<R>

jOOQ 3.18 solution

In jOOQ 3.18, to-many path expressions and implicit path correlations aren't yet available, so you have to hand-roll the correlation. You could use a semi-join, for example, if your RDBMS supports correlating derived tables:

context
    .select(
        ENGAGEMENT_NOTE,
        multiset(
            selectFrom(ENGAGEMENT_NOTE_ATTENDEE)
            .where(ENGAGEMENT_NOTE_ATTENDEE.ID.in(
                select(ENGAGEMENT_NOTE_ATTENDANCE.ATTENDEE_ID)
                .from(ENGAGEMENT_NOTE_ATTENDANCE)
                .where(ENGAGEMENT_NOTE_ATTENDANCE.ENGAGEMENT_NOTE_ID
                    .eq(ENGAGEMENT_NOTE.ID))
            ))
        ).`as`("attendees")
    .from(ENGAGEMENT_NOTE)
    .where(ENGAGEMENT_NOTE.ID.eq(noteId.value))
    .fetchOne()