JOOQ查询以JOIN ON WITH子句 [英] JOOQ query to JOIN ON WITH clause

问题描述

如何编写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"))

1. DSL.tableByName() 已过时.通常建议使用 table(Name) .
2. 这种动态构造的Table不知道其任何field()引用，因此table(name("a")).field("x")将返回null
3. 编译错误是由于您的ID引用的类型为Field<Integer>(可能是)，因此是类型的情况下，jOOQ API/Java编译器会推断出Field<Object>，这是无效的.

1. DSL.tableByName() is deprecated. It is generally recommended to use table(Name) instead.
2. Such a dynamically constructed Table does not know of any of its field() references, thus table(name("a")).field("x") will return null
3. 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()))

即使用

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

这篇关于JOOQ查询以JOIN ON WITH子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！