在JOOQ DSL中将PosgreSQL array_agg与连接别名一起使用 [英] Using PosgreSQL array_agg with join alias in JOOQ DSL

问题描述

我想将此SQL查询转换为JOOQ DSL.

I want to convert this SQL query to JOOQ DSL.

select "p".*, array_agg("pmu") as projectmemberusers
from "Projects" as "p"
join "ProjectMemberUsers" as "pmu" on "pmu"."projectId" = "p"."id"
group by "p"."id";

目前，我已经尝试使用JOOQ做类似的事情:

Currently i have tried doing something like this using JOOQ:

val p = PROJECTS.`as`("p")
val pmu = PROJECTMEMBERUSERS.`as`("pmu")
val query = db.select(p.asterisk(), DSL.arrayAgg(pmu))
        .from(p.join(pmu).on(p.ID.eq(pmu.PROJECTID)))
        .groupBy(p.ID)

这不起作用，因为DSL.arrayAgg需要输入Field<T>类型的内容.

This does not work because DSL.arrayAgg expects something of type Field<T> as input.

我是JOOQ的新手，而不是SQL专业人员.高度赞赏详细的解释和改进建议.

I am new to JOOQ and not an SQL professional. Detailed explanations and impovement suggestions are highly appreciated.

推荐答案

首先，语法确实有效，请在SQL Fiddle中进行检查:

First of all, the syntax indeed works, checked this in SQL Fiddle: http://sqlfiddle.com/#!17/e45b7/3

但是没有详细记录: https://www. postgresql.org/docs/9.5/static/functions-aggregate.html https://www.postgresql.org/docs/current/static/rowtypes.html#ROWTYPES-USAGE

But it's not documented in detail: https://www.postgresql.org/docs/9.5/static/functions-aggregate.html https://www.postgresql.org/docs/current/static/rowtypes.html#ROWTYPES-USAGE

这可能是jOOQ当前不支持此功能的原因:

That's probably the reason jOOQ doesn't support this currently: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/DSL.java#L16856

当前唯一可用的语法是单个字段:DSL.arrayAgg(pmu.field(1))

The only syntax that will work currently is with a single field: DSL.arrayAgg(pmu.field(1))

这篇关于在JOOQ DSL中将PosgreSQL array_agg与连接别名一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！