jOOQ：“错误：关系CTE不存在”。 [英] jOOQ: "error: relation CTE does not exist"

问题描述

我使用jOOQ 3.11.2和Postgres 9.4，试图在第二个CTE的定义中重用一个jOOQ CTE。

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.

每个StackOverflow问题如何在jOOQ中重新使用一个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) );

基于以上答案，我完全省略了cteTwo中的with（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);

上面的代码可以编译，但运行时出错：'jooq：错误的SQL语法。 ...错误：关系\ cteOne\不存在。'查看生成的SQL，在cteTwo中没有对cteOne的引用。

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.

然后我尝试将与（cteOne）子句放在cteTwo定义内的不同位置。在SELECT子句之前只能尝试三个地方。没有一个是正确的：

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) ); 

请注意，这与定义多个CTE，然后在最终的select语句中使用每个CTE不同，如下所示：

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?

推荐答案

我正在回答我的问题到2020年1月1日，不可能将CTE链接起来。正如我在评论中指出的，解决方法是使用临时数据库表，在我的情况下，这是代码的味道，最后我解决了更大的问题。问题，简化了我的代码，不需要解决链接的CTE。

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.

请注意，jOOQ是一个维护良好且不断发展的库，在以后的版本中可能会添加。

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

这篇关于jOOQ：“错误：关系CTE不存在”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！