1

Using jOOQ 3.11.2 and Postgres 9.4, I am trying to re-use one jOOQ CTE in the definition of a second CTE.

The following is incorrect per StackOverflow question How to re-use one CTE in another CTE in jOOQ

   CommonTableExpression<...> cteTwo = name().fields().as(select( with(cteOne ... ).from(cteOne) );

Based on the above answer I omitted the with(cteOne) clause in cteTwo completely. I tried:

  import static org.jooq.impl.DSL.*;

  CommonTableExpression<...> cteOne = name().fields().as(select(...); 
  CommonTableExpression<...> cteTwo = name().fields().as(select().from(cteOne) ); // seem to need with(cteOne) here. 

      List<someDTO> result = create
                 .with(cteOne)
                 .with(CteTwo)
                 .select(...)
                  .from(cteTwo)
                  .fetchInto(someDTO.class);

The above compiles but runs with error: 'jooq: bad SQL grammar. ... ERROR: relation \"cteOne\" does not exist.' Looking at the generated SQL, there is no reference to cteOne within cteTwo.

Then I tried putting with(cteOne) clause in different places within inside the cteTwo definition. There are only three more places before the SELECT clause to try. None were correct:

  CommonTableExpression<...> cteTwo = with(cteOne).name().fields().as(select(  ... ).from(cteOne) ); 
  CommonTableExpression<...> cteTwo = name() with(cteOne).fields().as(select(  ... ).from(cteOne) ); 
  CommonTableExpression<...> cteTwo = name().fields().with(cteOne).as(select(  ... ).from(cteOne) );

Please note that this different from defining several CTE's and then using each CTE in a final select statement like so:

 CommonTableExpression<...> cteAlpha = name(...).fields(...).as(select(...);
 CommonTableExpression<...> cteBeta = name(...).fields(...).as(select(...);

  List<SomeDTO> list = create
  .with(cteAlpha)
  .with(cteBeta)
  .select(...) from(cteAlpha, cteBeta).fetchInto(SomeDTO.class);

I haven't found documentation or examples covering this precise point. How should this be done?

peterJ
  • 63
  • 6
  • @LukasEder I found a workaround was to persist the results of CTE as a temp table. It's a two edged sword though. Makes it easier to visualise the result, but clutters the database and requires extra housekeeping to delete the temp files. I am still interested to know if there is an answer to the question above. – peterJ Jan 01 '20 at 19:42
  • Perhaps this isn't a preferable solution, but would inline CTEs be another workaround? By that I mean if "b" needs to select from "a", then it would have to be set-up that "b" would select from the same exact definition as "a" as opposed to selecting from the temporary name "a". This would get pretty cumbersome if there are multiple levels of nesting, but I figured I would ask – Torc Jun 10 '20 at 15:58
  • Thank you for the suggestion - that might work, but I think it defeats the purpose of defining a CTE in the first place. The use of temp database tables as a workaround was also a code smell. In the end I stepped back from the trees and looked at the woods. The bigger problem was to verify that summary reports manually entered from many different offices were fully logged. I found that it was much simpler to just add another graph series line to existing report graphs and let the user do the verification visually. I ended up not using jOOQ in this solution. – peterJ Jun 17 '20 at 17:16

1 Answers1

1

I am answering my own question. As of 1 Jan 2020, it was not possible to chain the CTEs. As I noted in the comments, the workaround was to use temp database tables. In my case that was a code smell. In the end I solved the bigger problem, simplified my code and did not need to solve chaining CTEs.

If you do use temp tables, remember to delete the tables when no longer needed to prevent database clutter.

Note that jOOQ is a well maintained evolving library and in later versions this might be added.

peterJ
  • 63
  • 6