Is working with a "derived" table possible with JooQ?

Nico van de Kamp

unread,

Aug 11, 2024, 3:19:16 PM8/11/24

to jOOQ User Group

I'm new to Jooq. I have written a query in Sql:

UPDATE agenda_item
SET begindatum = '<new value>', einddatum = '<new value>', version = (agenda_item.version + 1)
FROM agenda_item_type
,(SELECT agenda_item.id
,agenda_item.begindatum
,agenda_item_type.hele_dag
FROM agenda_item
,agenda_item_type
WHERE agenda_item_type.id = agenda_item.item_type_id) AS heleDagEvent -- Derived table name
WHERE agenda_item_type.id = agenda_item.item_type_id
AND agenda_item.agenda_id = '<agenda id parameter>'
AND heleDagEvent.id = '<agenda_item_id parameter>'
AND agenda_item.begindatum = heleDagEvent.begindatum
AND ((heleDagEvent.hele_dag = true)
OR (agenda_item.id = '<agenda_item_id parameter>'
AND heleDagEvent.hele_dag = false)
)
;

I have tried ths to covert this with jooq converter

But I get the message:

derived tables are not yet supported. See known limitations: https://github.com/jOOQ/jOOQ/issues/11911

usingDSL { context ->
context
.update(AGENDA_ITEM)
.set(AGENDA_ITEM.BEGINDATUM, agendaItem.begindatum)
.set(AGENDA_ITEM.EINDDATUM, agendaItem.einddatum)
.set(AGENDA_ITEM.BEGINTIJD, agendaItem.begintijd)
.set(AGENDA_ITEM.EINDTIJD, agendaItem.eindtijd)
.set(AGENDA_ITEM.RECORD_VERSION, agendaItem.recordVersion + 1)
.from(AGENDA_ITEM_TYPE
/* derived tables are not yet supported. See known limitations: https://github.com/jOOQ/jOOQ/issues/11911 */
,(((AGENDA_ITEM.ID,
AGENDA_ITEM.BEGINDATUM,
AGENDA_ITEM_TYPE.HELE_DAG)
.from(AGENDA_ITEM,
AGENDA_ITEM_TYPE)
.where(AGENDA_ITEM_TYPE.ID.eq(AGENDA_ITEM.ITEM_TYPE_ID))).as("heleDagEvent"))
.and (AGENDA_ITEM_TYPE.ID.eq(AGENDA_ITEM.ITEM_TYPE_ID)
.and(AGENDA_ITEM.AGENDA_ID).eq(agendaId.value) //agenda.id
.and(heleDagEvent.id).eq(agendaItem.id.value)
.and(AGENDA_ITEM.BEGINDATUM).eq(heleDagEvent.BEGINDATUM)
.and((heleDagEvent.HELE_DAG).eq(true)
.or(AGENDA_ITEM.ID).eq(agendaItem.id.value)
.and(heleDagEvent.HELE_DAG).eq(false)
)
).execute

Is this not possible? Can someone help or push in the direction how to solve this?

Nico van de Kamp

unread,

Aug 11, 2024, 5:04:07 PM8/11/24

to jOOQ User Group

I've been searching and googling and..

override fun update( agendaItem : AgendaItem, agendaId: AgendaId, zaakId: ZaakId): Int =

val derivedTable = select(AGENDA_ITEM.ID,
AGENDA_ITEM.BEGINDATUM,
AGENDA_ITEM_TYPE.HELE_DAG
)
.from(AGENDA_ITEM
,AGENDA_ITEM_TYPE)
.where(AGENDA_ITEM_TYPE.ID).eq(AGENDA_ITEM.ITEM_TYPE_ID)                     // this where gives an compiler error
.asTable("heleDagEvent")

val updateQuery = usingDSL { context ->
context.update(AGENDA_ITEM)
.set(AGENDA_ITEM.BEGINDATUM, agendaItem.begindatum)                          // The agendaItem signature parameter is not recognized
.set(AGENDA_ITEM.EINDDATUM, agendaItem .einddatum)
.set(AGENDA_ITEM.BEGINTIJD, agendaItem .begintijd)
.set(AGENDA_ITEM.EINDTIJD, agendaItem .eindtijd)
.set(AGENDA_ITEM.RECORD_VERSION, agendaItem .recordVersion + 1)
.from(
AGENDA_ITEM_TYPE, derivedTable
)
.where(
(AGENDA_ITEM_TYPE.ID).eq(AGENDA_ITEM.ITEM_TYPE_ID)
.and(derivedTable.id).eq(agendItem.ID)
.and(AGENDA_ITEM.AGENDA_ID).eq( agendaItem .id.value)
.and(AGENDA_ITEM.BEGINDATUM).eq(derivedTable.begindatum)
.and(
((derivedTable.hele_dag).eq(true))
.or(
(AGENDA_ITEM.ID).eq( agendaItem .ID)
.and(derivedTable.hele_dag).eq(false)
)
)
)

context.execute(updateQuery)                                                     // I don't know how todo this, exequeting the query?
}

Lukas Eder

unread,

Aug 12, 2024, 2:09:57 AM8/12/24

to jooq...@googlegroups.com

Thanks for your message. The hint about derived tables not being supported is a bit unclear. They're not supported (yet) in the translator when rendering jOOQ (Java) code. They are supported in jOOQ, though, as you seem to have found out:

https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/derived-tables/

I've created an issue to improve this warning message:

https://github.com/jOOQ/jOOQ/issues/17040

Regarding your other questions:

.where(AGENDA_ITEM_TYPE.ID).eq(AGENDA_ITEM.ITEM_TYPE_ID) // this where gives an compiler error

Just look at the parentheses, and you'll figure it out.

.set(AGENDA_ITEM.BEGINDATUM, agendaItem.begindatum) // The agendaItem signature parameter is not recognized

Is this really a jOOQ problem, or a problem with your kotlin usage?

context.execute(updateQuery) // I don't know how todo this, exequeting the query?

You just call updateQuery.execute()

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9041c3ac-df2b-40cf-a7d4-0166966aa792n%40googlegroups.com.

Nico van de Kamp

unread,

Aug 13, 2024, 1:12:14 PM8/13/24

to jOOQ User Group

Ok it's working now but I'm not satesfied espcially about the set statments. On this moment I don't know a better way but if somenone has an advise would be nice.

override fun update(agendaItem: AgendaItem, agendaId: AgendaId, zaakId: ZaakId): Int = usingDSL { context ->
// Define the derived table
val derivedTable = context.select(AGENDA_ITEM.ID,
AGENDA_ITEM.BEGINDATUM,
AGENDA_ITEM_TYPE.HELE_DAG,
AGENDA_ITEM_TYPE.NAAM
)
.from(AGENDA_ITEM, AGENDA_ITEM_TYPE)
.where(AGENDA_ITEM_TYPE.ID.eq(AGENDA_ITEM.ITEM_TYPE_ID))
.asTable("derivedTable")

context.update(AGENDA_ITEM)
.set(AGENDA_ITEM.BEGINDATUM, agendaItem.begindatum)

.set(AGENDA_ITEM.EINDDATUM, choose(AGENDA_ITEM_TYPE.HELE_DAG)
.`when`(true, agendaItem.einddatum)
.`when`(false, agendaItem.begindatum))

.set(AGENDA_ITEM.BEGINTIJD, choose(agendaItem.itemType.naam.equals("value"))
.`when`(true, AGENDA_ITEM.BEGINTIJD)
.`when`(false, agendaItem.begintijd))
.set(AGENDA_ITEM.EINDTIJD, choose(agendaItem.itemType.naam.equals("value"))
.`when`(true, AGENDA_ITEM.EINDTIJD)
.`when`(false, agendaItem.eindtijd))

.set(AGENDA_ITEM.AANGEKONDIGD, choose(AGENDA_ITEM.ID.eq(agendaItem.id.value)) // I'm doubting if this is the best way...?
.`when`(true, agendaItem.aangekondigd)
.`when`(false, AGENDA_ITEM.AANGEKONDIGD))

.set(AGENDA_ITEM.FUNCTIONARIS_BEDRIJF_BEPAALD, choose(AGENDA_ITEM.ID.eq(agendaItem.id.value))
.`when`(true, agendaItem.functionarisDoorBedrijfBepaald)
.`when`(false, AGENDA_ITEM.FUNCTIONARIS_BEDRIJF_BEPAALD))

.set(AGENDA_ITEM.OMSCHRIJVING, choose(AGENDA_ITEM.ID.eq(agendaItem.id.value))
.`when`(true, agendaItem.omschrijving)
.`when`(false, AGENDA_ITEM.OMSCHRIJVING))

.set(AGENDA_ITEM.TOELICHTING, choose(AGENDA_ITEM.ID.eq(agendaItem.id.value))
.`when`(true, agendaItem.toelichting)
.`when`(false, AGENDA_ITEM.TOELICHTING))

.set(AGENDA_ITEM.RECORD_VERSION, AGENDA_ITEM.RECORD_VERSION + 1)
.from(AGENDA_ITEM_TYPE, derivedTable)
.where(
AGENDA_ITEM_TYPE.ID.eq(AGENDA_ITEM.ITEM_TYPE_ID)
.and(derivedTable.field(AGENDA_ITEM.ID)?.eq(agendaItem.id.value))
.and(AGENDA_ITEM.AGENDA_ID.eq(agendaId.value))
.and(AGENDA_ITEM.BEGINDATUM.eq(derivedTable.field(AGENDA_ITEM.BEGINDATUM)))
.and(derivedTable.field(AGENDA_ITEM_TYPE.NAAM)?.equal("value")
?.or(AGENDA_ITEM.ID.eq(agendaItem.id.value)
.and(derivedTable.field(AGENDA_ITEM_TYPE.NAAM)?.notEqual( "value"))
)
)
).execute()
}

Op maandag 12 augustus 2024 om 08:09:57 UTC+2 schreef lukas...@gmail.com:

Lukas Eder

unread,

Aug 13, 2024, 1:16:22 PM8/13/24

to jooq...@googlegroups.com

Yeah, I don't know why you're passing the boolean value to SQL when you could just write a dynamic SQL query instead:

https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/

write:

.set(COLUMN, condition ? ifTrue : ifFalse)

You'll probably need to explicitly wrap bind values using DSL.val()

https://www.jooq.org/doc/latest/manual/sql-building/bind-values/indexed-parameters/

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/052d9b0f-6e00-4e43-a8ce-b810fd96a28bn%40googlegroups.com.

Nico van de Kamp

unread,

Aug 26, 2024, 10:47:57 AM8/26/24

to jOOQ User Group

Hello,

I was a week off,

"...Yeah, I don't know why you're passing the boolean value to SQL when you could just write a dynamic SQL query instead:..."

I don't exactly what you by dynamic SQL. What does this change? Or do you mean the ternairy operator by dynamic SQL as you mentioned?

I've tried this.

.set(AGENDA_ITEM.ITEM_TYPE_ID, AGENDA_ITEM.ID.eq(agendaItem.id.value) ? DSL.val(agendaItem.itemType.id.value) : DSL.val(AGENDA_ITEM.ITEM_TYPE_ID))

The question mark I get the error "expecting ')'. I have placed on different positions '(' and ')'

At the ':' I get the message "expecting an element"

I have tried with and without DSL.val. With DSL.val I get the message "Expecting an element - Unexpected tokens (use ';' to separate expressions on the same line)"

And set is given error as on the screenshot, but this is I suppose a Kotlin error

Op dinsdag 13 augustus 2024 om 19:16:22 UTC+2 schreef lukas...@gmail.com:

.set error.png

Lukas Eder

unread,

Aug 26, 2024, 10:53:23 AM8/26/24

to jooq...@googlegroups.com

I mean this:

.set(AGENDA_ITEM.BEGINTIJD, choose(agendaItem.itemType.naam.equals("value"))
.`when`(true, AGENDA_ITEM.BEGINTIJD)
.`when`(false, agendaItem.begintijd))

The equals method is evaluated in your Java code, so you already know (in Java!) whether your expression will be true or false. So, why implement this with SQL when you could write

agendaItem.itemType.naam.equals("value") ? AGENDA_ITEM.BEGINTIJD : val(agendaItem.begintijd)

This obviously doesn't work with the jOOQ eq() method, which returns a Condition, not a boolean

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6a9014f2-c6ab-4ce9-9105-363055c3ca54n%40googlegroups.com.

Reply all

Reply to author

Forward