Can't get my SpeakerRecord entity when joining with jOOQ

Question

I have three tables: event, speaker, event_speaker

Events and speakers have an n:m relationship managed by the "event_speaker" table. I used the jOOQ maven codegen to generate files like "EventRecord" and "SpeakerRecord".

In my application I want to get all speakers for a particular event. So I need to join the "speaker" table with the "event_speaker" table to be able to limit the results by the event id:

return dsl.select(SPEAKER.asterisk())
        .from(SPEAKER)
        .leftJoin(EVENT_SPEAKER).on(SPEAKER.ID.eq(EVENT_SPEAKER.SPEAKER_ID))
        .where(EVENT_SPEAKER.EVENT_ID.eq(eventId))
        .fetch();

Now I'll get a Result<Record> as the return value. But I would like to get a Result<SpeakerRecord> as the return value. If I remove the join, I will get it (but of course the result set will contain all speakers, which I don't want).

How is it possible to get a SpeakerRecord in place of the more general Record object when I need a join?

Answer 1

You can use fetchInto to tell jOOQ what result you are expecting:

return dsl.select(SPEAKER.fields())
    .from(SPEAKER)
    .leftJoin(EVENT_SPEAKER).on(SPEAKER.ID.eq(EVENT_SPEAKER.SPEAKER_ID))
    .where(EVENT_SPEAKER.EVENT_ID.eq(eventId))
    .fetchInto(SpeakerRecord.class);

Answer 2

Your LEFT JOIN is effectively an INNER JOIN because your WHERE clause filters on the outer joined table, but what you really wanted was a semi join. In the rare case you don't have a unique key on EVENT_SPEAKER (SPEAKER_ID, EVENT_ID), e.g. because there's a third column in the key, you would get duplicates with your current query.

So, do this instead:

return dsl
    .selectFrom(SPEAKER)
    .where(SPEAKER.ID.in(
        select(EVENT_SPEAKER.SPEAKER_ID)
        .from(EVENT_SPEAKER)
        .where(EVENT_SPEAKER.EVENT_ID.eq(eventId))
    ))
    .fetch();

Assuming, as always

import static org.jooq.impl.DSL.*;

With the above query, you can use DSLContext.selectFrom(Table) again, which produces your desired SpeakerRecord.

In other cases where you really need to join, use Simon's approach