Search code examples
javapostgresqlforeign-keysjooq

How to access foreign key table in jooq?

I have two tables: real_estate: id - PK, external_id_mdm

key_transfer: id - PK, real_estate_id - FK (references real_estate (id))

in my jooq update query i want to update and get returning type:


    dsl.update(keyTransfer)
                    .set(keyTransfer.SEND_OGO, ogo)
                    .where(keyTransfer.ID.eq(keyTransferId))
                    .returning(keyTransfer.realEstate().EXTERNAL_ID_MDM)
                    .fetchOneInto(String.class);

Which gives me error: Column "alias_30603682"."external_id_mdm" has been requested through the returning() clause, which is not present in table "key_transfer".

The message says something like I am accessing the key_transfer table, but indeed i am accessing real_estate through FK.

Few times in past i requested such kind of request in Select queries, and it worked. What's wrong this time?

I am expecting to get field external_id_mdm through FK as String type.

Solution

  • As of jOOQ 3.19, implicit join from DML RETURNING clauses is not yet supported, see:

    You have to join the REAL_ESTATE table explicitly using UPDATE .. FROM, like this:

    dsl.update(KEY_TRANSFER)
   .set(KEY_TRANSFER.SEND_OGO, ogo)
   .from(REAL_ESTATE)
   .where(KEY_TRANSFER.ID.eq(keyTransferId))
   .and(KEY_TRANSFER.REAL_ESTATE_ID.eq(REAL_ESTATE.ID))
   .returningResult(REAL_ESTATE.EXTERNAL_ID_MDM)
   .fetchOneInto(String.class);

    Note that in order to fetch a Record1<String> consisting of only the EXTERNAL_ID_MDM column, you'll have to use returningResult(), not returning(). See the relevant Javadoc for details.