Jooq, postgres и обновление и возврат данных из многослойных таблиц не удается

Question

Я поставлен в тупик при формулировке запроса jooq для возврата данных из двух таблиц при обновлении поля в одной из этих таблиц. (Postgres 9,6, Jooq 3,11)

Таблицы DEVICE и CUSTOMER объединяются с помощью ограничения внешнего ключа DEVICE.CUSTOMERID = CUSTOMER.ID.

Я хочу вернуть единственный подходящий device.id и customer.secret пользователя устройства и обновить device.state до IN_PROGRESS. Приемлемость оценивается различными пунктами.

Я начал с

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(CUSTOMER)
         .where(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))
         .and(DEVICE.STATE.eq(StateEnum.NEW))
         .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();    

Это привело к ERROR: Field ("public"."customer"."secret") is not contained in Row

Я смоделировал другой запрос, основанный на этом предложении Postgres RETURNING с помощью join, но в результате получил ту же ошибку.

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(CUSTOMER, db
         .select()
         .from(CUSTOMER
            .join(DEVICE).on(CUSTOMER.ID.eq(DEVICE.CUSTOMERID)))
         .forUpdate()
      )
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

 ERROR:  Field ("public"."customer"."secret") is not contained in Row  

Я попытался несколько перестановок и в результате с ошибкой некоторые варианты ошибок

• имя таблицы "<customer/device>" указано более одного раза
• отсутствует запись FROM-предложения для таблицы "<customer/device>"

Я уверен, что такой запрос можно сделать, но у меня закончились идеи. Какие-либо предложения?

Репрезентативный список вариантов, которые я пробовал

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(
          DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID)))
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: table name "device" specified more than once

  final Record task = dbContext
  .update(DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID)))
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: syntax error at or near "join"

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(db.select().from(CUSTOMER,
          DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))).forUpdate()
      )
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: table name "customer" specified more than once

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(db.select().from(
          DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))).forUpdate()
      )
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: missing FROM-clause entry for table "customer"


  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)          

    .from(CUSTOMER.join(DEVICE).on(CUSTOMER.ID.eq(DEVICE.CUSTOMERID)))
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: table name "device" specified more than once

Answer 1

Это из-за старой ошибки проектирования в jOOQ API, где returning() предложение приводит к типу возврата Result<R> или же R, а не фактические столбцы, которые вы выбираете. Таким образом, вы можете возвращать столбцы только из обновляемой таблицы, но не из каких-либо других таблиц или произвольных выражений. Из-за обратной совместимости это не могло быть легко изменено.

Тем не менее, начиная с jOOQ 3.11, теперь вы можете позвонить returningResult() В качестве обходного пути, чтобы получить точный тип строки, который вы указываете: https://github.com/jOOQ/jOOQ/issues/7475