jOOQ Dynamic WITH Clause

Question

I am unable to find documentation on how to dynamically construct WITH Clauses (i.e. Common Table Expressions / CTEs) in jOOQ. My use case is this :

• I need to nest dynamically created columns in order to produce new data from those dynamically created columns
• For example, I am creating a new field which is the non-null result from a full outer join. This field is ONLY available at the time of the query execution, so I need to store it within a WITH clause to reference it in other queries for additional calculations.
• Ideally, I would be able to request a WITH clause type query dynamically, and this dependency can be sorted out by placing this joined dataset in its own CTE to use in downstream references.

I am attempting to use the following, with no luck:

    SelectQuery<Record> query =
        getQuery(
            dslContext,
            selectFields,
            fromClause,
            groupFields,
            conditionClause,
            orderFields,
            query.getOffset(),
            query.getLimit());

    // WORKS JUST FINE
    Supplier<Stream<Map<String, Object>>> results = () ->
        query
            .fetchStream()
            .map(Record::intoMap);

    // make a nested query here. static for now.
    // DOES NOT WORK
    Supplier<Stream<Map<String, Object>>> resultsWith =
        () ->
            DSL.with("s1")
                .as(query) // Shouldn't I be able to reference a SelectQuery here?
                .select()
                .from(table(name("s1")))
                .fetchStream()
                .map(Record::intoMap);

query.toString() looks something like this:

select 
  table1.field1,
  coalesce( 
    table1.id, 
    table2.id) as table1.id_table2.id, 
  count(*) as table2.field1.count, 
  sum(table2.field2) as table2.field2.sum
  from table1.table1 as table1
  full outer join table2.table2 as table2
  on table1.id = table2.id
  where table1.field2 < 3000.0
  group by 
  table1.id_table2.id,
  table1.field1
  order by table1.field1 asc
  limit 100

What I would like to do at a minimum is to reference the coalesced field above in additional downstream queries. Ideally I would not be limited at all in the manner or number of dynamic references I could make when constructing a WITH clause in jOOQ. In the end, I want to be able to dynamically create queries such as these, which show CTEs referencing CTEs too :

-- WITH Clause usage is preferrable
with
  myFirstSelection as (
    select
      (id + 100) as newfield
    from table1.table1 n
  ),
  mySecondSelection as (
    select
      (newField + 200) as newerField
    from myFirstSelection
  )
select
* 
from mySecondSelection
;

-- Inline queries, while not ideal, would be permissible
select 
* 
from (
  select
  (newField + 200) as newerField
  from (
    select
      (id + 100) as newField
    from table1.table1 n
  ) as myFirstSelection
) as mySecondSelection
;

Would that even be possible? Or am I limited to static tables and static selections?

---

LINKS

stackoverflow

• 'WITH' Clause not supported
• JOIN on a CTE in jOOQ
• Reference CTE in another CTE #1
  Reference CTE in another CTE #2

jOOQ Website

• Building SQL : WITH Clause
• v3.13 Manual : WITH Clause
  WithStep Javadocs

GitHub

• Old CTETests repository which has been removed
• Issue 454 : Add CTE Support
• Issue 3174 : Add CTE Support w/ DML
• Issue 3175 : Nested CTE
• Issue 4474 : Form CTE from Plain SQL

jOOQ Google Group

• How to handle names etc. for Common Table Expressions?
• Using arbitrary SQL string as a CTE in conjunction with the DSL
• WITH RECURSIVE CTE in Postgres
• CTEs, recursive CTEs and JOOQ
• Recursive CTE
• Recursive CTE

Answer 1

The issue turned out to be an issue with Jackson deserialization in a property in my JSON payload which gets turned into my query. I was getting a NullPointerException when one of the properties was getting converted into a part of the query class. With regards to jOOQ, this example works fine. As an addition, this is a testing query which sums the first field by referencing relative position in the result-set :

    /* Output looks like this : 
    +-----------------+
    |sum_of_everything|
    +-----------------+
    |              100|
    +-----------------+
     */

    Supplier<Stream<Map<String, Object>>> resultsWith =
        () ->
            dslContext
                .with("s1")
                .as(query)
                .select(sum(field("1", Integer.class)).as("sum_of_everything"))
                .from(table(name("s1")))
                .fetchStream()
                .map(Record::intoMap);

This supplier can be returned as a response in a RESTful framework to stream results back to the requestor.