如何通过在聚合数组中检查特定元素来使用JOOQ获取结果集?

huangapple go评论75阅读模式
英文:

How to get result set by checking a specific element in an aggregated array using JOOQ?

问题

rs = dslContext.select(
                field("user_id"),
                field("gardens_array"),
                field("province_array"),
                field("district_array"))
        .from(table(select(
                arrayAggDistinct(field("garden")).as("gardens_array"),
                arrayAggDistinct(field("province")).as("province_array"),
                arrayAggDistinct(field("distict")).as("district_array"))
                        .from(table("lst.user"))
                        .leftJoin(table(select(
                                field("section.user_id").as("user_id"),
                                field("garden.garden").as("garden"),
                                field("garden.province").as("province"),
                                field("garden.distict").as("distict"))
                                .from(table("lst.section"))
                                .leftJoin("lst.garden")
                                .on(field("section.garden").eq(field("garden.garden")))
                                .leftJoin("lst.district")
                                .on(field("district.district").eq(field("garden.district")))).as("lo"))
                        .on(field("user.user_id").eq(field("lo.user_id")))
                        .groupBy(field("user.user_id"))).as("joined_table"))
        .where(val(2).equal(DSL.any("district_array")))
        .fetch()
        .intoResultSet();
英文:

I want to filter results by a specific value in the aggregated array in the query.

Here is a little description of the problem.
Section belongs to the garden. Garden belongs to District and District belongs to the province.
Users have multiple sections. Those sections belong to their gardens and they are to their Districts and them to Province.

I want to get user ids that have value 2 in district array.
I tried to use any operator but it doesn't work properly. (syntax error)
Any help would be appreciated.

ps: This is possible writing using plain SQL

rs = dslContext.select(
field("user_id"),
field("gardens_array"),
field("province_array"),
field("district_array"))
.from(table(select(
arrayAggDistinct(field("garden")).as("gardens_array"),
arrayAggDistinct(field("province")).as("province_array"),
arrayAggDistinct(field("distict")).as("district_array"))
.from(table("lst.user"))
.leftJoin(table(select(
field("section.user_id").as("user_id"),
field("garden.garden").as("garden"),
field("garden.province").as("province"),
field("garden.distict").as("distict"))
.from(table("lst.section"))
.leftJoin("lst.garden")
.on(field("section.garden").eq(field("garden.garden")))
.leftJoin("lst.district")
.on(field("district.district").eq(field("garden.district")))).as("lo"))
.on(field("user.user_id").eq(field("lo.user_id")))
.groupBy(field("user.user_id"))).as("joined_table"))
.where(val(2).equal(DSL.any("district_array"))
.fetch()
.intoResultSet();

答案1

得分: 0

你的代码调用了 DSL.any(T...),这对应于 PostgreSQL 中的表达式 any(?),其中绑定值在你的情况下是一个 String[]。但是你不希望 "district_array" 是一个绑定值,你希望它是一个列引用。因此,要么将你的 arrayAggDistinct() 表达式分配给一个局部变量并重用它,要么重新使用你的 field("district_array") 表达式或者复制它:

val(2).equal(DSL.any(field("district_array", Integer[].class)))

请注意,在使用 plain SQL 模板化 API 时,明确指定数据类型(例如 Integer[].class)通常是一个很好的做法,或者更好的办法是使用代码生成器。

英文:

Your code is calling DSL.any(T...), which corresponds to the expression any(?) in PostgreSQL, where the bind value is a String[] in your case. But you don't want "district_array" to be a bind value, you want it to be a column reference. So, either, you assign your arrayAggDistinct() expression to a local variable and reuse that, or you re-use your field("district_array") expression or replicate it:

val(2).equal(DSL.any(field("district_array", Integer[].class)))

Notice that it's usually a good idea to be explicit about data types (e.g. Integer[].class) when working with the plain SQL templating API, or even better, use the code generator.

huangapple
  • 本文由 发表于 2020年8月27日 00:00:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/63601397.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定