|Table1| 1 n |Table2| 1 n |Table 3|
| | --------------> | | --------------> | |
| | | | | |
-------- -------- --------
(experiment) (tags) (TagDescription)
I am trying to map the data from the above three one-to-many linked tables to the DTOs below. However, the Jooq query is not working as expected. Values of table 1 and table 3 are mapped correctly but table 2 values are all Null. Here are the DTOs.
class ExperimentDto {
long id;
String name;
List<TagDto> tags;
}
class TagDto {
long id;
long experimentId;
String name;
List<TagDescriptionDto> tagDescription;
}
class TagDescriptionDto {
long id;
long tadId;
String description;
}
... and here is my query. I took reference from this answer to write my query.
dslContext.select(
Experiment.asterisk(), // map all the Experiment table field to DTO
field(
select(
jsonArrayAgg(jsonObject(
jsonEntry(jsonObject(Tag.fields())), // map all the Tag table field to DTO + ONE-to-Many with TagDescription but these fields are all NULL
jsonEntry("tagDescription", field(
select(jsonArrayAgg(jsonObject(TagDescription.fields())))
.from(orderDetailAddition)
.where(TagDescription.ID.eq(Tag.ID))))
)
)
).from(Tag)
.where(Tag.eq(Experiment.ID))
).as("tags")
).from(Experiment)
.where(Experiment.NAME.eq("Name"))
.fetchInto(ExperimentDto.class);
Could somebody give me hint what is missing and wrong in the query. Apparently, this part have some issues jsonEntry(jsonObject(Tag.fields())).
Moreover, is it a good way (jsonmappers in query) to read and map such relations on DTO or we can do it in a better way using Jooq? (can't use multiset because I am on 3.14)
Update:
Updated to JOOQ 3.15.4 and started using MULTISET, but now I am getting BAD SQL exceptions on the simplest query. I wrote this query that is throwing an error. For some reason, it is unable to find experiment.id inside the nested query.
SELECT
experiment.id,
experiment.name,
(SELECT
COALESCE(JSON_MERGE_PRESERVE('[]',
CONCAT('[',
GROUP_CONCAT(JSON_ARRAY(v0)
SEPARATOR ','),
']')),
JSON_ARRAY())
FROM
(SELECT
tags.name AS v0
FROM
tags
WHERE
(tags.experiment_id = experiment.id)) AS t) AS tags
FROM
experiment
WHERE
experiment.id > 0;
Error Code: 1054. Unknown column 'experiment.id' in 'where clause'
MySQL version: 5.7.35
