How to re-use one CTE in another CTE in jOOQ

Question

In jOOQ am re-using a CTE in a later CTE. I am trying to summarise student completion records by year and school. I am using jOOQ 3.11.2 and postgres 9.4.

I have working SQL code. However in jOOQ, I am getting null values returned.

This appears to be a problem with how I am re-using one CTE in a later CTE.

At first, I thought it might be a problem with the use of count(). From the manual, it looks like count() is being used correctly. As a test, I removed all reference to count() in the query and still get the same error.

I could not find examples of reusing or chaining CTEs in jOOQ. Easy enough in SQL, as shown here: SQL - Use a reference of a CTE to another CTE but I haven't got the hang of it in jOOQ.

When run in debug mode on Intellij, I see an error that the select() statement cannot be evaluated in the second CTE.

Cannot evaluate org.jooq.impl.SelectImpl.toString()

Here is a minimal example showing what I am doing.

 CommonTableExpression<Record4<String, String, String, Year>> cteOne = name("CteOne")
    .fields("SCHOOL","STUDENT_NAME", "COURSE_COMPLETED", "YEAR_COMPLETED")
    .as(
            select( a.NAME.as("SCHOOL")
                    , a.STUDENT_NAME
                    , a.COURSE_DESCRIPTION.as("courseCompleted"),
                    , a.YEAR_COMPLETED 
                    )
                    .from(a)
                    .orderBy(a.YEAR_COMPLETED)
    );

CommonTableExpression<Record3<String, Year, Integer >> cteCounts = name("cteCounts")

    .fields("SCHOOL", "YEAR_COMPLETED", "NUM_COMPLETED" )

    .as( with(cteOne)
                    .select(
                            , field(name("cteOne","SCHOOL"), String.class)
                            , field(name("cteOne","YEAR_COMPLETED"), Year.class)
                            , count().as("NUM_COMPS_LOGGED")
                    )
                    .from(cteOne)
                    .groupBy(
                            field(name("cteCompsList","YEAR_COMPLETED"), Year.class)
                          , field(name("cteOne","SCHOOL"), String.class)
                    )
                    .orderBy(
                            field(name("cteCompsList","YEAR_COMPLETED"), Year.class)
                          , field(name("cteOne","SCHOOL"), String.class)
                    )
    );

Can someone please point me in the right direction on this?

score 2 · Answer 1 · answered Dec 09 '19 at 08:56

2

Just like in your plain SQL version of your query, your cteCounts should not have a with(cteOne) clause:

WITH 
  cteOne (columns...) AS (select...),
  cteCounts (columns...) AS (select referencing cteOne, no "with cteOne" here...)
SELECT ...
FROM ...

Remove it and your query should be fine

answered Dec 09 '19 at 08:56

Lukas Eder

211,314
129
689
1,509

Thankyou. I deleted the with( cteOne) clause & got an error that the SQL produced by jooq doesn't have a reference to cteOne. I tried other places within cteOne such as: ```with(cteOne).name(...)``` then ```name(...).with(cteOne).fields(...)``` then ```name(...).fields(...).with(cteOne).as(...)```. Intellij says that none are correct. Doing something like: ```Result> result2 = create().with(t1) .with(t2) .select( ...).from(t1, t2).fetch()```; works fine, but doesn't the cteCounts also need a ```with()``` somewhere in the definition of the CTE? – peterJ Dec 10 '19 at 04:10
@peterJ: Thanks a lot for your comment. Would you mind asking a new question? The Stack Overflow comment formatting makes it difficult to follow what you might've been doing since my answer. – Lukas Eder Dec 10 '19 at 08:37
yes, the comment format makes it hard to follow. I have posted a new question here: https://stackoverflow.com/questions/59271846/jooq-error-relation-cte-does-not-exist – peterJ Dec 10 '19 at 16:40
@peterJ: Thanks. Will look into it in a bit – Lukas Eder Dec 11 '19 at 08:12
@LukasEder Isn't it a case that some dialects support [nested cte](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=6c34a51c4824f2a500da9596e35aaa03)? Could you post the "plain" SQL version how this "non-working" query would look like? – Lukasz Szozda Jan 01 '20 at 08:52
@LukaszSzozda: I don't think the OP was going to nest CTE. I'll be happy to answer your own, separate question on Stack Overflow... – Lukas Eder Jan 01 '20 at 17:04

How to re-use one CTE in another CTE in jOOQ

1 Answers1

Linked