jOOQ Dynamic Number of WITH Clauses

Question

I am playing around with jOOQ and nesting queries. I have a JSON payload which may contain many subqueries. I want to treat these subqueries as a variable number of Common Table Expressions (i.e. CTE's in the WITH clause). Currently I have this working example, but it is static in terms of the number of CTE's it includes. How would I accomplish a variable number of CTE's within the WITH Clause?

    /*
    +-----------------+
    |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);

Ultimately I will need to deserialize the JSON payload to ensure that the CTE reference hierarchy works properly, and I will need to see if jOOQ supports referencing one CTE in another CTE before selecting the final result. I will need to accomplish something like this :

    /*
    +-----------------+
    |sum_of_everything|
    +-----------------+
    |              100|
    +-----------------+
     */
    Supplier<Stream<Map<String, Object>>> resultsWith =
        () ->
            dslContext
                .with("s1").as(query1)
                .with("s2").as(query2) // should be able to reference "s1" i.e. query1
                ...
                .with("sNMinus1").as(queryNMinus1)
                .with("sN").as(queryN) // should be able to reference any upstream CTE
                .select(sum(field("1", Integer.class)).as("sum_of_everything"))
                .from(table(name("sN")))
                .fetchStream()
                .map(Record::intoMap);

Answer 1

You can create a CommonTableExpression instance starting out from a Name, using Name.as(Select) e.g.

CommonTableExpression<?> s1 = name("s1").as(query1);
CommonTableExpression<?> s2 = name("s2").as(query2);

// And then (or, of course, use a Collection)
dslContext.with(s1, s2, ..., SN)