英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论