[SOLVED] jooq select sintax

jooq select sintax

There are two tables. Each client has 2 addresses. I need to select the client name and string of addresses. I'm confused about jooq syntax, how select basic_address and additional_address for TABLE RESULT

Table CLIENT id | name | BASIK_OFICE_ID | ADDITIONAL_OFICE_ID

Table OFICE id | address

TABLE RESULT name | basic_address | additional_address

public record ClientAndOfficeData(
    String name,
    String basic_address,
    String additional-address) {}


 var content =
        context
            .select(
                CLIENT.NAME,
                OFFICE.ADDRESS,
                OFFICE.ADDRESS)
            .from(CLIENT)
            .innerJoin(OFICE)
            .on(OFFICE.ID.eq(CLIENT.BASIK_OFFICE_ID))
            .or(OFFICE.ID.eq(CLIENT.ADDITIONAL_OFFICE_ID))
            .fetchInto(ClientAndOfficeData.class);
  }

Solution

This isn't really a jOOQ question but a generic SQL question. You're trying to do something like a PIVOT query, but only with a fixed number of 2 columns. So, you have to join the table twice:

Office basic = OFFICE.as("basic");
Office additional = OFFICE.as("additional");

var content = context
    .select(
        CLIENT.NAME,
        basic.ADDRESS,
        additional.ADDRESS)
    .from(CLIENT)
    .leftJoin(basic)
        .on(basic.ID.eq(CLIENT.BASIK_OFFICE_ID))
    .leftJoin(additional)
        .on(additional.ID.eq(CLIENT.BASIK_OFFICE_ID))
    .fetchInto(ClientAndOfficeData::new);

This approach uses type safe table aliases based on your generated code. I used LEFT JOIN instead of INNER JOIN, assuming that the office IDs may be optional.

Another, perhaps simpler approach would be to use implicit joins:

var content = context
    .select(
        CLIENT.NAME,
        CLIENT.basicOffice().ADDRESS,
        CLIENT.additionalOffice().ADDRESS)
    .from(CLIENT)
    .fetchInto(ClientAndOfficeData::new);

This assumes that you have named your foreign keys BASIC_OFFICE and ADDITIONAL_OFFICE. If the keys are named differently, there will be different methods than basicOffice() and additionalOffice().