jooq多表查询_jooq错误:嵌套查询(sum和group by)表的子句条目中缺少

只有少数SQL方言(例如Oracle)支持未命名的派生表,而不是全部(例如PostgreSQL中不支持)。这就是为什么jooq为每个派生表生成别名的原因。既然派生表有别名,就不能再使用原始表名访问其列,但必须使用别名。您可以在生成的SQL查询中看到错误的地方:

select

"alias_88420990"."ledger_id", -- These are correctly referenced, because you used

"alias_88420990"."amount" -- select(), so jOOQ did the dereferencing for your

from (

select

"public"."test_safes_funds_allocation"."ledger_id",

sum("public"."test_safes_funds_allocation"."amount") as "amount"

from "public"."test_safes_funds_allocation"

group by "public"."test_safes_funds_allocation"."ledger_id"

) as "alias_88420990" -- This alias is generated by jOOQ

-- In these predicates, you're referencing the original column name with full qualification

-- when you should be referncing the column from alias_88420990 instead

where ("public"."test_safes_funds_allocation"."amount" >= ? and 1 = 1)

order by "public"."test_safes_funds_allocation"."amount" asc

limit ?

标准固定

因此,可以这样重写jooq查询,以便正确地获取表名:

// Create a local variable to contain your subquery. Ideally, provide an explicit alias

Table> t = table(

select(

TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,

sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)

.as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))

.from(TEST_SAFES_FUNDS_ALLOCATION)

.groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as("t");

// Now, use t everywhere, instead of TEST_SAFES_FUNDS_ALLOCATION

context.select()

.from(t)

.where(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))

.and(t.field(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID).notIn(excludedLedgers))

.orderBy(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())

.limit(1)

.fetchOne();

我在用

Table.field(Field)

从别名表中提取字段,而不丢失类型信息。

改进了修复,利用现有的表类型

考虑到派生的两列的名称也出现在原始表中,您可以使用“技巧”从jooq api中获得更多类型安全性,从而更方便地取消对列的引用。首先为表命名:

// This t reference now has all the column references like the original table

TestSafesFundsAllocation t = TEST_SAFES_FUNDS_ALLOCATION.as("t");

// The subquery is also named "t", but has a different definition

Table> subquery = table(

select(

TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,

sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)

.as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))

.from(TEST_SAFES_FUNDS_ALLOCATION)

.groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as(t);

// Now, select again from the subquery, but dereference columns from the aliased table t

context.select()

.from(subquery)

.where(t.AMOUNT.greaterOrEqual(amount))

.and(t.LEDGER_ID.notIn(excludedLedgers))

.orderBy(t.AMOUNT.asc())

.limit(1)

.fetchOne();

记住,这是一个技巧,只要派生表的列与派生表选择的原始表具有相同的名称和类型,它就可以工作。

重写SQL

派生表(和公共表表达式)是一个领域,在这个领域中,jooq的DSL并没有本机SQL那么强大,因为jooq不能以通常的方式轻松地对派生表进行类型检查。这就是必须使用局部变量和类型不安全取消引用的原因。

通常,如果这是一个选项,那么这个警告是完全避免派生表的充分理由。在您的情况下,它是不需要派生表的。更好的查询(在本机SQL中更好)是:

context.select(

TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,

sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))

.from(TEST_SAFES_FUNDS_ALLOCATION)

.where(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID.notIn(excludedLedgers))

.groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)

.having(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))

.orderBy(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())

.limit(1)

.fetchOne()