postgresql 如何在Jooq中查询元组上的不同数组聚合?

ztyzrc3y  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(114)

如何在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中使用的正确类型。

wdebmtf2

wdebmtf21#

有许多问题要求在整个API中更普遍地接受Row[N]类型,这些类型扩展了SelectField,但不是Field,参见例如:

  • #9123添加对min(Row[N])和max(Row[N])的支持
  • #10701添加对ARRAY_AGG()的支持
  • #11998审查API以更频繁地接受SelectField而不是Field

jOOQ没有广泛支持这些的主要原因是,很难向用户传达这些东西往往在PostgreSQL中工作,但在其他任何地方都很难,因此,例如Oracle,MySQL,SQL Server用户会经常对jOOQ API表面上承诺但没有实现的问题感到失望。
除非有一种方法可以完全模拟这些东西,否则您只能使用PostgresDSL(或您自己的plain SQL templates)来解决问题。

相关问题