如何在JOOQ多表关联查询中生成唯一学校列表

我有三张表：SchoolTable、SchoolOrgTable和SchoolDetailsTable，表间关系如下：

• SchoolTable与SchoolOrgTable为一对多关系。
• SchoolOrgTable与SchoolDetailsTable为多对一关系。

当前使用的JOOQ查询语句：

SelectJoinStep<Record> result = dsl.select()
    .from(SchoolTable)
    .join(SchoolOrgTable)
    .on(SchoolTable.A_ID.eq(SchoolOrgTable.ATest_ID))
    .leftJoin(SchoolOrgTable)
    .on(SchoolTable.A_ID.eq(SchoolOrgTable.B_Id))
    .leftJoin(SchoolDetailsTable)
    .on(SchoolDetailsTable.C_ID.eq(SchoolOrgTable.B_ID));

当前查询结果（存在冗余） #

[
  {
    "schoolId": 1,
    "schoolName": "JaySchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 1,
        "detailsName": "Test",
        "details": "schoolIsGood"
      }
    ]
  },
  {
    "schoolId": 1,
    "schoolName": "JaySchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 2,
        "detailsName": "Test1",
        "details": "awesome"
      }
    ]
  },
  {
    "schoolId": 2,
    "schoolName": "TermSchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 3,
        "detailsName": "Test",
        "details": "Nice"
      }
    ]
  }
]

期望结果（聚合SchoolDetails） #

[
  {
    "schoolId": 1,
    "schoolName": "JaySchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 1,
        "detailsName": "Test",
        "details": "schoolIsGood"
      },
      {
        "detailsID": 2,
        "detailsName": "Test1",
        "details": "awesome"
      }
    ]
  },
  {
    "schoolId": 2,
    "schoolName": "TermSchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 3,
        "detailsName": "test3",
        "details": "Nice"
      }
    ]
  }
]

1. 修正JOIN逻辑 #

你的查询重复关联了SchoolOrgTable，这会直接导致数据膨胀。先调整关联逻辑，通过SchoolOrgTable一次性关联SchoolTable和SchoolDetailsTable：

SelectJoinStep<Record> result = dsl.select()
    .from(SchoolTable)
    .join(SchoolOrgTable)
    .on(SchoolTable.A_ID.eq(SchoolOrgTable.ATest_ID))
    .leftJoin(SchoolDetailsTable)
    .on(SchoolDetailsTable.C_ID.eq(SchoolOrgTable.B_ID));

2. 客户端聚合（用JOOQ fetchGroups） #

如果要将同一学校的SchoolDetails聚合到列表中，可以使用JOOQ的fetchGroups方法按学校主键分组，再手动组装目标结构：

// 按学校记录分组，对应其所有详情记录
Map<Record, List<Record>> grouped = result.fetchGroups(
    r -> r.into(SchoolTable),
    r -> r.into(SchoolDetailsTable)
);

// 转换为期望的JSON结构
List<Map<String, Object>> finalResult = new ArrayList<>();
grouped.forEach((schoolRecord, detailsRecords) -> {
    Map<String, Object> schoolMap = new HashMap<>();
    schoolMap.put("schoolId", schoolRecord.get(SchoolTable.SCHOOL_ID));
    schoolMap.put("schoolName", schoolRecord.get(SchoolTable.SCHOOL_NAME));
    schoolMap.put("isActive", schoolRecord.get(SchoolTable.IS_ACTIVE));
    
    List<Map<String, Object>> detailsList = detailsRecords.stream()
        .map(dr -> {
            Map<String, Object> detailsMap = new HashMap<>();
            detailsMap.put("detailsID", dr.get(SchoolDetailsTable.DETAILS_ID));
            detailsMap.put("detailsName", dr.get(SchoolDetailsTable.DETAILS_NAME));
            detailsMap.put("details", dr.get(SchoolDetailsTable.DETAILS));
            return detailsMap;
        })
        .collect(Collectors.toList());
    
    schoolMap.put("SchoolDetails", detailsList);
    finalResult.add(schoolMap);
});

3. 数据库层聚合（支持分组查询） #

如果需要按schoolName或detailsName分组，推荐直接在数据库层用聚合函数完成（适合MySQL 8+、PostgreSQL等支持JSON的数据库），减少客户端数据处理量：

// MySQL示例：用JSON_ARRAYAGG聚合详情数据
SelectConditionStep<Record> result = dsl.select(
        SchoolTable.SCHOOL_ID,
        SchoolTable.SCHOOL_NAME,
        SchoolTable.IS_ACTIVE,
        // 聚合生成SchoolDetails的JSON数组
        field("JSON_ARRAYAGG(JSON_OBJECT('detailsID', {0}, 'detailsName', {1}, 'details', {2}))", 
            JSON.class, 
            SchoolDetailsTable.DETAILS_ID, 
            SchoolDetailsTable.DETAILS_NAME, 
            SchoolDetailsTable.DETAILS)
    )
    .from(SchoolTable)
    .join(SchoolOrgTable)
    .on(SchoolTable.A_ID.eq(SchoolOrgTable.ATest_ID))
    .leftJoin(SchoolDetailsTable)
    .on(SchoolDetailsTable.C_ID.eq(SchoolOrgTable.B_ID))
    // 按学校主键、名称等分组，若要按schoolName分组，调整GROUP BY字段即可
    .groupBy(SchoolTable.SCHOOL_ID, SchoolTable.SCHOOL_NAME, SchoolTable.IS_ACTIVE);

// 转换结果
List<Map<String, Object>> finalResult = result.fetch().stream()
    .map(r -> {
        Map<String, Object> schoolMap = new HashMap<>();
        schoolMap.put("schoolId", r.get(SchoolTable.SCHOOL_ID));
        schoolMap.put("schoolName", r.get(SchoolTable.SCHOOL_NAME));
        schoolMap.put("isActive", r.get(SchoolTable.IS_ACTIVE));
        schoolMap.put("SchoolDetails", r.get(3, JSON.class).toList());
        return schoolMap;
    })
    .collect(Collectors.toList());

内容的提问来源于stack exchange，提问作者EveryDayLearner