Finally, the request below works.
I did my best to avoid One to Many, having Many to One instead. That’s why I started from TreeFieldValuePO. There is no subquery left.
Do you think it does the same thing as the initial request ?
.from(TreeFieldValuePO.class, "fv")
.from(DocumentGroupPO.class, "dg") // second root required
// fv → document
.innerJoin("fv.document", "d")
// d → fieldValueSet
.innerJoin("d.fieldValueSet", "fvs") // this is where fieldValueSet really is
// dg → group
.innerJoin("dg.group", "g")
// correlate dg to document (no inverse on AbstractDocumentPO)
.whereExpression("dg.document.id = d.id")
// type constraint
.where("TYPE(g)").eqLiteral(ScopeFunctionPO.class)
// external filters
.where("d.id").in(docIds)
.where("g.id").in(personalIds)
// correlations
.whereExpression("dg.group.id = TREAT(g AS ScopeFunctionPO).function.id")
.whereExpression("fv.value.id = g.id")
.whereExpression("fv.field.id = TREAT(g AS ScopeFunctionPO).scope.id")
.select("d.id")
.endSet();
2025-12-03 07:16:59,948 INFO [stdout] (default task-2) Hibernate: /* SELECT d.id FROM DocumentGroupPO dg JOIN dg.document d JOIN d.confidentialityLevel cl WHERE d.id IN (:param_0) AND (cl.levelType = 0 OR dg.group.id IN (:param_1))
2025-12-03 07:16:59,948 INFO [stdout] (default task-2) UNION
2025-12-03 07:16:59,948 INFO [stdout] (default task-2) SELECT d.id FROM TreeFieldValuePO fv JOIN fv.document d JOIN d.fieldValueSet fvs, DocumentGroupPO dg JOIN dg.group g WHERE dg.document.id = d.id AND TYPE(g) = ScopeFunctionPO AND d.id IN (:param_2) AND g.id IN (:param_3) AND dg.group.id = g.function.id AND fv.value.id = g.id AND fv.field.id = g.scope.id */ select d1_0.p_iddoc from p_doc_group dgp1_0 join p_doc d1_0 on d1_0.p_iddoc=dgp1_0.p_iddoc join p_conf_level cl1_0 on cl1_0.p_idconf_level=d1_0.p_idconf_level where d1_0.p_iddoc in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and (cl1_0.p_level_type=0 or dgp1_0.p_idgroup in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) union select d2_0.p_iddoc from p_field_value tfvp1_0 join p_doc d2_0 on d2_0.p_iddoc=tfvp1_0.p_iddoc join p_field_value fvs1_0 on d2_0.p_iddoc=fvs1_0.p_iddoc,p_doc_group dgp2_0 join p_group g2_0 on g2_0.p_idgroup=dgp2_0.p_idgroup where dgp2_0.p_iddoc=d2_0.p_iddoc and g2_0.p_group_type=6 and d2_0.p_iddoc in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and g2_0.p_idgroup in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and g2_0.p_idgroup=g2_0.p_idfunction and tfvp1_0.p_idtree_content_value=g2_0.p_idgroup and tfvp1_0.p_idparam_field=g2_0.p_idscope and tfvp1_0.p_field_type=8