链接生成的Jooq类时如何解决歧义匹配 [英] How to resolve ambiguous match when chaining generated Jooq classes

问题描述

我已经在JPA中定义了模型，并为我的应用程序编写了一些查询，并且我正在使用JOOQ生成的类将所有表连接在一起，以检查所请求的资源是否实际上属于发出请求的用户.

I have defined my models in JPA and am writing some queries for my application and I am using JOOQ generated classes to join all the tables together to check if the requested resources actually belong to the requesting user.

但是，当我这样做时，会收到以下警告:

However, when I do this I get the following warning:

Ambiguous match found for ID. Both "alias_4548634"."ID" and "alias_47496750"."ID" match.

java.sql.SQLWarning: null                                                                                                                                       
        at org.jooq.impl.Fields.field(Fields.java:132) ~[jooq-3.11.10.jar:?]
... etc

这是我的代码

db.select(countField)
  .from(thing)
  .where(JThing.THING.thingBucket().bucket().organization().customer().ID.in(idList))
  .orderBy(countField)

这是它生成的SQL

SELECT 
  count(PUBLIC.THING.ID) AS count
FROM (
  PUBLIC.THING
    LEFT OUTER JOIN (
      PUBLIC.THING_BUCKET AS alias_72652126
        LEFT OUTER JOIN (
          PUBLIC.BUCKET AS alias_4548634
            LEFT OUTER JOIN (
              PUBLIC.ORGANISATION AS alias_43016761
                LEFT OUTER JOIN PUBLIC.CUSTOMER AS alias_47496750
                ON alias_43016761.CUSTOMER_ID = alias_47496750.ID
            )
            ON alias_4548634.ORGANISATION_ID = alias_43016761.ID
        )
        ON alias_72652126.ID = alias_4548634.ID
    )
    ON PUBLIC.THING.THING_BUCKET_ID = alias_72652126.ID
  )
WHERE alias_47496750.ID IN (81353)
ORDER BY count

鉴于JOOQ正在生成SQL，我希望它能够理解它而不会引发错误.我想念什么?如何配置/查询/以任何方式解决SQLWarning?

Given that JOOQ is generating the SQL I'd expect it to be able to understand it without throwing an error. What am I missing? How do I do configure/query/whatever to resolve the SQLWarning?

更新

玩转之后，我确定了问题的根源.

After playing around I've identified the source of the issue.

THING_BUCKET是BUCKET的子类型，因此THING_BUCKET.ID = BUCKET.ID

THING_BUCKET is sub-type of BUCKET so that THING_BUCKET.ID = BUCKET.ID

如果我将查询重写为相同的结果，但没有错误

if I rewrite the query to I get the same results, but without the error

SELECT 
  count(PUBLIC.THING.ID) AS count
FROM (
  PUBLIC.THING
        LEFT OUTER JOIN (
          PUBLIC.BUCKET AS alias_4548634
            LEFT OUTER JOIN (
              PUBLIC.ORGANISATION AS alias_43016761
                LEFT OUTER JOIN PUBLIC.CUSTOMER AS alias_47496750
                ON alias_43016761.CUSTOMER_ID = alias_47496750.ID
            )
            ON alias_4548634.ORGANISATION_ID = alias_43016761.ID
        )
    ON PUBLIC.THING.BUCKET_ID = alias_4548634.ID
  )
WHERE alias_47496750.ID IN (81353)
ORDER BY count

所以我想做的就是去

db.select(countField)
  .from(thing)
  .where(JThing.THING.bucket().organization().customer().ID.in(idList))
  .orderBy(countField)

直接将我的东西加入BUCKET，然后再加入THING_BUCKET，但是我不知道如何使用生成的类来完成此工作.

and join my THING directly to the BUCKET rather then the THING_BUCKET, but I do not know how to accomplish this with the generated classes.

推荐答案

这看起来像是jOOQ 3.14中已修复的错误，请参见#10603

This looks like a bug that has been fixed in jOOQ 3.14, see #8659, #10603

这篇关于链接生成的Jooq类时如何解决歧义匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！