JOOQ查询以JOIN ON WITH子句

本文介绍了JOOQ查询以JOIN ON WITH子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何编写JOOQ查询以加入"with"子句中的字段?

How can I write a JOOQ query to join on a field from a "with" clause?

例如，我尝试过:

create.with("a").as(select( val(1).as("x"), val("a").as("y") )) .select() .from(tableByName("a") .join(ANOTHER_TABLE) .on(ANOTHER_TABLE.ID.eq(tableByName("a").field("x"))) .fetch();

但是，由于编译器不知道tableByName("a").field("x")的类型，因此无法解析要使用的eq()方法.考虑到我知道的类型，有没有一种方法可以显式地提供它?还是我应该采用另一种方法来加入"with"子句中的字段?

However, as the compiler doesn't know the type of tableByName("a").field("x") it cannot resolve which eq() method to use. Given that I know the type, is there a way I can provide it explicitly? Or is there another approach I should take to join on a field from a "with" clause?

推荐答案

虽然我当然同意 flutter的答案是一条更可取的解决之道解决方案，在这里，我将快速添加一个答复，以回答您的特定编译错误问题.

While I certainly agree with flutter's answer being a more desireable path to a solution here, I'll just quickly add a response that answers your specific compilation error question.

您当前的连接谓词有三件事是错误的:

There are three things that are wrong with your current join predicate:

ANOTHER_TABLE.ID.eq(tableByName("a").field("x"))

DSL.tableByName() 已过时.通常建议使用 table(Name) .

这种动态构造的Table不知道其任何field()引用，因此table(name("a")).field("x")将返回null

编译错误是由于您的ID引用的类型为Field<Integer>(可能是)，因此是 Field.eq() 方法也需要一个Field<Integer>参数.在不了解字段"x"类型的情况下，jOOQ API/Java编译器会推断出Field<Object>，这是无效的.

DSL.tableByName() is deprecated. It is generally recommended to use table(Name) instead.

Such a dynamically constructed Table does not know of any of its field() references, thus table(name("a")).field("x") will return null

The compilation error is due to your ID reference being of type Field<Integer> (probably), and thus the Field.eq() method expects a Field<Integer> argument as well. Without any knowledge about the type of your field "x", the jOOQ API / Java compiler infers Field<Object>, which is invalid.

因此，解决方案是编写:

So, the solution would be to write:

// field(Name, Class) ANOTHER_TABLE.ID.eq(field(name("a", "x"), Integer.class)) // field(Name, DataType) ANOTHER_TABLE.ID.eq(field(name("a", "x"), ANOTHER_TABLE.ID.getDataType()))

即使用 DSL.field(Name, Class<T>) 或 DSL.field(Name, DataType<T>) (如果您使用的是自定义数据类型绑定/转换器).

I.e. to use DSL.field(Name, Class<T>), or DSL.field(Name, DataType<T>) if you're using custom data type bindings / converters.