Hi,
I converted the equivalent to this query to JOOQ:
select a.mediaId
from contentApplication a
join mediaRelationship r on r.childId = a.mediaId
join module md on md.mediaId = r.parentId
where md.mediaId = 'xyz'
and exists (
select 1
from sync_content as sc
join mediaRelationship r1 on r1.childId = sc.content_media_id --
content -> collection
join mediaRelationship r2 on r2.childId = r1.parentId -- collection ->
anthology
where
r2.parentId = a.mediaId
and sc.module_media_id = md.mediaId
union
select 1
from sync_content as sc
join mediaRelationship r1 on r1.childId = sc.content_media_id --
content -> anthology
where
r1.parentId = a.mediaId
and sc.module_media_id = md.mediaId
)
The SQL code that JOOQ generates is:
select `a`.`mediaId`
from `contentApplication` as `a`
join `mediaRelationship` as `r`
on `r`.`childId` = `a`.`mediaId`
join `module` as `md`
on `md`.`mediaId` = `r`.`parentId`
where (
`md`.`mediaId` = 'xyz'
and exists (
select *
from (
select 1
from `sync_content` as `sc`
join `mediaRelationship` as `r1`
on `r1`.`childId` = `sc`.`content_media_id`
join `mediaRelationship` as `r2`
on `r2`.`childId` = `r1`.`parentId`
where (
`r2`.`parentId` = `a`.`mediaId`
and `sc`.`module_media_id` = `md`.`mediaId`
)
) x
union
select *
from (
select 1
from `sync_content` as `sc`
join `mediaRelationship` as `r1`
on `r1`.`childId` = `sc`.`content_media_id`
where (
`r1`.`parentId` = `a`.`mediaId`
and `sc`.`module_media_id` = `md`.`mediaId`
)
) x
)
)
Note the wrapping on the statements in the union, which I believe is due to
"In case your database doesn't support ordered UNION subselects, the
subselects are nested in derived tables" described
in
https://www.jooq.org/doc/3.9/manual/sql-building/sql-statements/select-statement/union-clause
(and I'm using MySQL). The problem is that the correlations
(`r2`.`parentId` = `a`.`mediaId`) fail because jOOQ inserted a level.
Is there a way around this (force JOOQ to not nest, or restructure)?
Thanks,
Eelco
--
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].
For more options, visit https://groups.google.com/d/optout.
