如何在JooQ中正确实现元组上的去重数组聚合查询？

我想知道如何用jOOQ正确表达以下SQL查询。我了解arrayAggDistinct() DSL方法，但在表示行类型时遇到了困难。

对应的SQL语句：

SELECT
    u.user_id,
    ARRAY_AGG(DISTINCT (g.group_id, g.group_name)) AS grouped_groups,
    ARRAY_AGG(DISTINCT (a.authority_id, a.authority)) AS grouped_authorities
FROM users u
         LEFT JOIN group_members gm ON u.user_id = gm.user_id
         LEFT JOIN groups g ON gm.group_id = g.group_id
         LEFT JOIN group_authorities ga ON g.group_id = ga.group_id
         LEFT JOIN authorities a ON ga.authority_id = a.authority_id
GROUP BY u.user_id;

编辑补充：我找到了能生成正确查询的代码，但其中的rowField方法已被标记为过时，而推荐的rowN替代类型无法用于arrayAggDistinct方法。

现有代码示例：

val groupRow: Row2<Long?, String?> = row(GROUPS.GROUP_ID, GROUPS.GROUP_NAME)
val groupsField: Field<Record2<Long?, String?>> = PostgresDSL.rowField(groupRow)
val authorityRow: Row2<Long?, String?> = row(AUTHORITIES.AUTHORITY_ID, AUTHORITIES.AUTHORITY)
val authoritiesField: Field<Record2<Long?, String?>> = PostgresDSL.rowField(authorityRow)
val jooqQuery = PostgresDSL.select(
    *USERS.fields(),
    arrayAggDistinct(groupsField).`as`("groups"),
    arrayAggDistinct(authoritiesField).`as`("authorities"),
).from(USERS)
    .leftJoin(GROUP_MEMBERS).on(USERS.USER_ID.eq(GROUP_MEMBERS.USER_ID))
    .leftJoin(GROUPS).on(GROUP_MEMBERS.GROUP_ID.eq(GROUPS.GROUP_ID))
    .leftJoin(GROUP_AUTHORITIES).on(GROUPS.GROUP_ID.eq(GROUP_AUTHORITIES.GROUP_ID))
    .leftJoin(AUTHORITIES).on(GROUP_AUTHORITIES.AUTHORITY_ID.eq(AUTHORITIES.AUTHORITY_ID))
.where(USERS.MAIL.eq(mail))
.groupBy(*USERS.fields())

针对rowField过时的问题，你可以直接使用DSL.row()结合asField()方法，或者用DSL.field()包裹行表达式，将行类型转换为Field实例，这样就能正常传入arrayAggDistinct()了。

修改后的代码示例 #

// 直接将行表达式转为Field，替代过时的rowField
val groupsField: Field<Record2<Long?, String?>> = 
    row(GROUPS.GROUP_ID, GROUPS.GROUP_NAME).asField()
val authoritiesField: Field<Record2<Long?, String?>> = 
    row(AUTHORITIES.AUTHORITY_ID, AUTHORITIES.AUTHORITY).asField()

val jooqQuery = PostgresDSL.select(
    *USERS.fields(),
    arrayAggDistinct(groupsField).`as`("groups"),
    arrayAggDistinct(authoritiesField).`as`("authorities"),
).from(USERS)
    .leftJoin(GROUP_MEMBERS).on(USERS.USER_ID.eq(GROUP_MEMBERS.USER_ID))
    .leftJoin(GROUPS).on(GROUP_MEMBERS.GROUP_ID.eq(GROUPS.GROUP_ID))
    .leftJoin(GROUP_AUTHORITIES).on(GROUPS.GROUP_ID.eq(GROUP_AUTHORITIES.GROUP_ID))
    .leftJoin(AUTHORITIES).on(GROUP_AUTHORITIES.AUTHORITY_ID.eq(AUTHORITIES.AUTHORITY_ID))
.where(USERS.MAIL.eq(mail))
.groupBy(*USERS.fields())

更简洁的内嵌写法 #

你也可以直接在arrayAggDistinct()参数中完成行表达式的转换，省去单独定义字段的步骤：

val jooqQuery = PostgresDSL.select(
    *USERS.fields(),
    arrayAggDistinct(row(GROUPS.GROUP_ID, GROUPS.GROUP_NAME).asField()).`as`("groups"),
    arrayAggDistinct(row(AUTHORITIES.AUTHORITY_ID, AUTHORITIES.AUTHORITY).asField()).`as`("authorities"),
).from(USERS)
    .leftJoin(GROUP_MEMBERS).on(USERS.USER_ID.eq(GROUP_MEMBERS.USER_ID))
    .leftJoin(GROUPS).on(GROUP_MEMBERS.GROUP_ID.eq(GROUPS.GROUP_ID))
    .leftJoin(GROUP_AUTHORITIES).on(GROUPS.GROUP_ID.eq(GROUP_AUTHORITIES.GROUP_ID))
    .leftJoin(AUTHORITIES).on(GROUP_AUTHORITIES.AUTHORITY_ID.eq(AUTHORITIES.AUTHORITY_ID))
.where(USERS.MAIL.eq(mail))
.groupBy(*USERS.fields())

原理说明 #

row()返回的RowN类型可以通过asField()方法转换为Field<RecordN>，而arrayAggDistinct()接受Field类型参数，这样既完美替代了过时的rowField方法，也符合jOOQ的最新API规范。

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