Hi all,
I am a bit new to JOOQ and the whole concept it brings and have been
reading and exploring a lot. One thing I am having trouble with (maybe I am
not utilizing it well enough not sure) is using multiset and multisetAgg.
I scoured the JOOQ documentatation and every question and issue I could
find that was related to those 2, but couldn't find my answer.
My stack is Vert.x + JOOQx (wrapper for JOOQ for Vert.x) + JOOQ (v3.16.2) +
Kotlin + Postgres.
So my issue is this:
Code:
val casesQuery = jooq.dsl()
.select(
CASES.CASE_ID,
CASES.NAME,
CASES.DESCRIPTION,
multisetAgg(
DOCUMENTS.DOCUMENT_ID,
DOCUMENTS.NAME,
DOCUMENTS.DESCRIPTION
).`as`("documents").convertFrom {
it?.map {mapping(::Document)}
})
.from(CASES)
.leftJoin(REF_CASES_DOCUMENTS)
.on(REF_CASES_DOCUMENTS.CASE_ID.eq(CASES.CASE_ID))
.leftJoin(DOCUMENTS)
.on(REF_CASES_DOCUMENTS.DOCUMENT_ID.eq(DOCUMENTS.DOCUMENT_ID))
.where(CASES.CASE_ID.eq(id))
.groupBy(CASES.CASE_ID, CASES.NAME, CASES.DESCRIPTION)
val cases = jooq.execute(casesQuery,
DSLAdapter.fetchMany(casesQuery.asTable(), Case::class.java)).await()
This generates the following query:
select
"public"."cases"."case_id",
"public"."cases"."name",
"public"."cases"."description",
jsonb_agg(jsonb_build_array("public"."documents"."document_id",
"public"."documents"."name", "public"."documents"."description")) as
"documents"
from "public"."cases"
left outer join "public"."ref_cases_documents"
on "public"."ref_cases_documents"."case_id" = "public"."cases"."case_id"
left outer join "public"."documents"
on "public"."ref_cases_documents"."document_id" =
"public"."documents"."document_id"
where "public"."cases"."case_id" = 'ab49dc80-b787-48d4-ae7a-9cc455b3d08a'
group by "public"."cases"."case_id", "public"."cases"."name",
"public"."cases"."description"
Im trying to map the inner Object to Document data class, but the query
gets generated with "jsonb_build_array" instead of "jsonb_build_object" so
I get an InvalidCastExcetion.
Im using custom data classes with JPA Column annotations and not using DAOs
or POJOs generated from JOOQ. Its worth saying that underneath the wrappers
plain JOOQ is used.
All the documentation and posts I saw point to a generated SQL with
jsonb_build_object, but this is not true in my case. Is it some sort of
configuration or inferrence from my setup or something?
This is probably the only problem I need to solve, otherwise JOOQ looks
absolutely awesome, so good job to the team that is developing it!
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/51ebed22-9bb8-4cd8-9804-e24b5ff05ad4n%40googlegroups.com.
