common table expression - jOOQ CommonTableExpression with SelectQuery

Question

We have a query with a common structure that we use under different circumstances (where clause is different)

So we have something like this

val baseQuery: SelectQuery<Record> = dsl
  .select(someFields)
  .from(someTable)
  .join(otherTable).on(joinClause)
  .query

In other places we then extend this query however needed.

baseQuery.apply {
  addConditions(conditionsA)
}.fetch()

baseQuery.apply {
  addConditions(conditionsB)
}.fetch()

So far so good. But now it would be cool if we could somehow use this base in combination with a CTE. Not sure how to do that though.

val someCTE: CommonTableExpression<*> = DSL.....

// dsl
//   .with(someCTE)
//   .selectQuery(baseQuery.apply {}) ˉ\_(ツ)_/ˉ

baseQuery.apply {
  // addWith(someCTE) ˉ\_(ツ)_/ˉ
  addSelect(someCTE.field(cteField))
  addJoin(someCTE, joinClause)
  addConditions(conditionsC)
} 

Is there a way to do it? Perhaps other suggestions how to reuse the base query when using a CTE?

Edit: Solution

With the help of Lukas' answer I settled on this approach

fun dynamicQuery(
  context: SelectSelectStep<*> = dsl.select(),
  selects: List<Field<*>> = listOf(),
  joins: List<Pair<Table<*>, Condition>> = listOf(),
  conditions: List<Condition> = listOf()
): SelectQuery<Record>

So in normal customizations I can

dynamicQuery(
  conditions = conditionsA
).fetch()

dynamicQuery(
  conditions = conditionsB
).fetch()

It can be combined with a CTE

val someCTE: CommonTableExpression<*> = DSL.....
dynamicQuery(
  context = dsl.with(someCTE).select(),
  selects = listOf(someCTE.field(cteField)),
  joins = listOf(someCTE to joinClause),
  conditions = conditionsC
).fetch()

question from:https://stackoverflow.com/questions/65909646/jooq-commontableexpression-with-selectquery

Answer 1

TL;DR: You can't use CTEs with jOOQ 3.15's model API

Some background on the jOOQ model API vs DSL API distinction

The very old jOOQ 1.0 only had what is now called the "model API", a mutable, procedural API with setters (no getters), where you can manipulate dynamic SQL.

jOOQ 2.0 introduced the DSL API, which is what most people are using today. The fact that the DSL API mimicks the SQL language helps users discover jOOQ API much more easily. Everything is named exactly as expected. With the exception of a few quirks in the areas of CTE and derived tables, you can write jOOQ-SQL almost just like actual SQL.

The model API was not deprecated, but wrapped by the DSL API, and kept around:

• for backwards compatibility reasons
• because some people seemed to like the procedural approach

You can't do anything with the model API that you couldn't do with the DSL API as well, though a more functional programming style may be helpful when doing this with the DSL API. See: https://blog.jooq.org/2017/01/16/a-functional-programming-approach-to-dynamic-sql-with-jooq

The future of jOOQ

While the model API is still getting some new clauses support for SELECT, INSERT, UPDATE, DELETE statements, there are some statements that are not available in a model API form. These include MERGE, TRUNCATE, all DDL statements, all procedural statements. And, the WITH clause.

The strategy is to eventually deprecate the model API, because the redundancy creates a lot of extra work that is better invested elsewhere. There are also subtle bugs when people call model API methods in unexpected order, i.e. an order that is not possible through the DSL API.

• In a first step, pretty soon, jOOQ will inverse the relationship between APIs: https://github.com/jOOQ/jOOQ/issues/11241. The model API will be the auxiliary wrapper of the DSL API for those who rely on it for backwards compatibility. It isn't unlikely that the model API will even be extracted into a separate compatibility module, to discourage its use in new code
• In a next step, with the dependencies inversed, the DSL API can finally become consistently immutable, which is what many users expect, and to their surprise, find lacking: https://github.com/jOOQ/jOOQ/issues/9047
• Eventually, the model API will be deprecated, and then dropped

You can still use it today, and the deprecation and removal will be done over a long period of time, so there's no hurry in getting off this API (as always with jOOQ). But in the context of your question, it's good to see that jOOQ will not invest in adding too many features to it, anymore. CTE support won't be added to the model API.

Workarounds

You can, of course, work around this limitation, because internally, the model API is CTE capable:

• You could use reflection to add new CTEs to the SelectQuery internal representation. I won't document how this works, here, because it's never a good idea to document these things :)
• You could start creating a query using the DSL API, and then extract the internal SelectQuery representation using SelectFinalStep.getQuery(), and continue working from there.