CODEWITHSUNDEEP
javasqlaliasjava-8jooq
PL_kolek
PL_kolek

Reputation: 375

Jooq: Aliased tables and Record.into

I have two tables in my DB: Client and Address. Client references Address twice: as BillToAddress and ShipToAdress. I'd like to retrieve Client with his Addresses using jOOQ. That's my attempt:

public static Client fromRecord(Record record, Table<AddressRecord> billTo, Table<AddressRecord> shipTo) {
    return new Client(record.getValue(CLIENT.CLIENT_ID),
            AddressDao.fromRecord(record.into(billTo)),
            AddressDao.fromRecord(record.into(shipTo))
    );
}

public List<Client> loadAll() {
    superfirma.jooq.local.tables.Address billTo = ADDRESS.as("billTo");
    superfirma.jooq.local.tables.Address shipTo = ADDRESS.as("shipTo");
    return jooqContext.select().from(CLIENT)
                      .join(billTo).on(CLIENT.BILL_TO_ADDRESS_ID.eq(billTo.ADDRESS_ID))
                      .leftOuterJoin(shipTo).on(CLIENT.SHIP_TO_ADDRESS_ID.eq(shipTo.ADDRESS_ID))
                      .fetch(record -> fromRecord(record, billTo, shipTo));
}

It doesn't work: record.into matches by column names, not taking the (aliased) table name into account. From my quick look at jOOQ's code it just works that way (and I wonder why it doesn't work the way I want). The result is a Client with two identical addresses.

What's the alternative way of doing this?

Upvotes: 1

Views: 823

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221275

This is indeed a bug in jOOQ. I've registered #3634 for this.

The issue appears only with aliased self-joins as the Record.into(Table) isn't able to distinguish between the two tables that originate from the same one. As a workaround, you could manually split the record into two before loading it into AddressDao.fromRecord() again.

Upvotes: 1

Related Questions