如何在JooQ中正确地表达这个SQL查询？我确实知道arrayAgDistinct（）DSL方法，但我在表达行类型时遇到了麻烦。

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;

字符串

编辑我发现，这将创建正确的查询

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方法被标记为已弃用，并且建议的rowN替代方法不是在arrayAggDistinct中使用的正确类型。