联接多个表时，'on子句‘中的未知列'ideas_service.parent_section.id’

Question

因此，我尝试在一个查询中返回五个表

parent_section(id, gate_id)


Assessment_Question(id,parent_section_id)


Assessment_question_multi_choice_option(id, question_id)


Assessment_answer_Group(id, entity_id)


Assessment_Answer(id,assessment_question_id, assessment_answer_Group_id)

我正在尝试列出所有的问题

错误为Unknown column 'ideas_service.parent_section.id' in 'on clause'

下面是我的问题

        dsl.select()
        .from(PARENT_SECTION,ASSESSMENT_ANSWER_GROUP)
        .join(ASSESSMENT_QUESTION)
        .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
        .leftJoin(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION)
        .on(
            ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION.ASSESSMENT_QUESTION_ID.eq(
                ASSESSMENT_QUESTION.ID))
        .join(ASSESSMENT_ANSWER)
        .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
        .where(PARENT_SECTION.GATE_ID.eq(gateId))
        .fetch()

以前，当我仅尝试查询parent_Section_assessment_question和assessment_question_multi_choice这三个表的表时，我的查询是这样的，并且工作正常

    var queryResult =
    dsl.select()
        .from(PARENT_SECTION)
        .join(ASSESSMENT_QUESTION)
        .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
        .leftJoin(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION)
        .on(
            ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION.ASSESSMENT_QUESTION_ID.eq(
                ASSESSMENT_QUESTION.ID))
        .where(PARENT_SECTION.GATE_ID.eq(gateId))
        .fetch()

我应该如何修改工作查询以在问题assessment_answer.answer_group_id上列出assessment_Answer_Group(其中assessment_answer_Group.entity_id = x)之间的连接

澄清连接结构

我在parent_section和assessment_Question之间做内连接，在assessment_question和assessment_multi_choice_option之间做左连接

answer_group和assessment_answer之间的连接与这三个表是分开的，应该是where assessment_answer_group.entity_id =x和join on assessment_Answer.answer_Group_id = assessment_answer_Grorup.id

编辑:在akinas评论之后，我的查询现在是这个，它找到了assessment_answer_group，但没有找到任何assessment_answers

    var queryResult =
    dsl.select()
      //below line is cross join with an always true join coniditon
        .from(PARENT_SECTION.join(ASSESSMENT_ANSWER_GROUP).on(true))
        .join(ASSESSMENT_QUESTION)
        .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
        .leftJoin(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION)
        .on(
            ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION.ASSESSMENT_QUESTION_ID.eq(
                ASSESSMENT_QUESTION.ID))
        .join(ASSESSMENT_ANSWER)
        .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
        .where(PARENT_SECTION.GATE_ID.eq(gateId))

Answer 1

为什么会出现语法错误

出现此错误的原因：

错误是'on子句‘中的列'ideas_service.parent_section.id’未知

是因为您在jOOQ查询中将ASSESSMENT_QUESTION连接到ASSESSMENT_ANSWER_GROUP，而不是PARENT_SECTION：

dsl.select()
   .from(PARENT_SECTION,ASSESSMENT_ANSWER_GROUP)
   .join(ASSESSMENT_QUESTION)
   .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
   // ...

正如其他人在评论中指出的那样，您使用ANSI JOIN语法混合了列表中的逗号分隔。无论是在SQL中还是在jOOQ中，几乎都不推荐这样做。在jOOQ中，上面的查询对应于这个SQL：

SELECT *
FROM PARENT_SECTION, (
  ASSESSMENT_ANSWER_GROUP 
    JOIN ASSESSMENT_QUESTION 
      ON PARENT_SECTION.ID = ASSESSMENT_QUESTION.PARENT_ID
  )
..

正如您所看到的，连接表达式没有任何意义，因为您使用的谓词引用了一个不在作用域中的表来连接两个表。I recently wrote a blog post that might help understanding SQL JOIN syntax by adding such parentheses。我认为括号在这一点上绝对有帮助。

这是我关于正确使用joins的建议：

• 永远不会将表列表与ANSI连接混为一谈，这通常只会带来麻烦(尽管它在边缘情况下可能很有用)。如果需要外部联接，则不能在大多数SQL方言中使用表列表(Oracle除外)，因此无论如何都需要ANSI联接。坚持使用它们。
• 注意混合使用外部连接和内部连接。Joins是运算符，而it's important to be aware of their associativity

笛卡尔乘积

但你还有另一个问题。您不能在SQL中轻松地连接多对多关系，否则会得到笛卡尔积。含义：

• PARENT_SECTION可以有多个ASSESSMENT_QUESTION，也可以有多个ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION。这是两个级别的多对多关系，将复制每个ASSESSMENT_QUESTION的PARENT_SECTION和每个ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION的ASSESSMENT_QUESTION。这种重复重复是可以的，因为您沿着join tree.
• However，的一个分支往下走，ASSESSMENT_QUESTION也有几个ASSESSMENT_ANSWER，因为这是另一个多对多关系。这会引发问题。

因此，当您遍历连接树的两个独立的多个分支时，您最终会得到ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION和ASSESSMENT_ANSWER之间的笛卡尔积，这肯定不是您想要的。

虽然您的5-join查询在语法上可能最终是正确的，但在语义上它仍然是错误的！

把它弄对

你将不得不运行resort to using either XML/JSON (available from jOOQ 3.14)，或者(至少)运行2个查询：

// Query 1 producing ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION
dsl.select()
   .from(PARENT_SECTION)
   .join(ASSESSMENT_QUESTION)
     .on(ASSESSMENT_QUESTION.PARENT_SECTION_ID.eq(PARENT_SECTION.ID))
   .leftJoin(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION)
     .on(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION.ASSESSMENT_QUESTION_ID
       .eq(ASSESSMENT_QUESTION.ID))
   .where(PARENT_SECTION.GATE_ID.eq(gateId))
   .fetch();

// Query 2 producing ASSESSMENT_ANSWER and ASSESSMENT_ANSWER_GROUP
dsl.select()
   .from(ASSESSMENT_ANSWER)
   .join(ASSESSMENT_ANSWER_GROUP)
     .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
   .where(ASSESSMENT_ANSWER.ASSESSMENT_QUESTION_ID.in(
     select(ASSESSMENT_QUESTION.ID)
     .from(ASSESSMENT_QUESTION)
     .join(PARENT_SECTION)
     .on(ASSESSMENT_QUESTION.PARENT_SECTION_ID.eq(PARENT_SECTION.ID))
     .where(PARENT_SECTION.GATE_ID.eq(gateId))
   ))
   .fetch();

然后，您可以在客户端中将结果组合在一起。注意，第二个查询不再需要连接PARENT_SECTION和ASSESSMENT_QUESTION。一个(使用IN predicate实现的) semi join就足够了

使用隐式连接

jOOQ supports implicit joins for to-one relationships。上面的查询2也可以这样编写，这可能会简单一点(尽管我建议您先学习“真正的”连接)：

dsl.select()
   .from(ASSESSMENT_ANSWER)
   .join(ASSESSMENT_ANSWER_GROUP)
     .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
   .where(ASSESSMENT_ANSWER.assessmentQuestion().parentSection().GATE_ID.eq(gateId))
   .fetch();