Join Fetch same object to N level and join fetch different object on N level-CodePudding

So I want to fetch the whole XmlObject with tree structure. The object has parent-child OneToMany relation childObjects to himself and OneToMany relation to xmlPeriods. xmlPeriods are at last level only.

Tree is something like this.

XmlObject

  |_XmlObject

     |_XmlObject

       |_XmlObject

         |_XmlPeriod

Here are my Entity's relations.

@Entity
@Table(name = "xml_object")
public class XmlObject implements Serializable {

    @Fetch(value = FetchMode.SELECT)
    @BatchSize(size = 50)
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true)
    @JoinTable(
            name = "xml_object_tree",
            joinColumns = {@JoinColumn(name = "parent_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "child_id", referencedColumnName = "id")}
    )
    @ToString.Exclude
    private List<XmlObject> childObjects;

    @Fetch(value = FetchMode.SUBSELECT) // FetchMode.JOIN loads eagerly always
    @OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.LAZY, orphanRemoval = false)
    @JoinColumn(name = "xml_object_id", nullable = false)
    @ToString.Exclude
    private List<XmlPeriod> xmlPeriods;

I've started with this query.

    @Query(value = "select xo.id from XmlObject xo "  
            " where xo.objectType = :type and bw_and(xo.status, :status) = 0")
    Optional<List<Long>> findAllObjectsIdsOfTypeAndNotInStatusesAndNotDeletedN(
            ObjectTypeEnum type, Integer status, Pageable pageable);

bw_and - it is bitwise and applied via custom Postgres dialect

I don't want to set fetch = FetchType.EAGER, I don't want to hide the problem under Set to suppress error below

cannot simultaneously fetch multiple bags

I can't use two query solution from Vlad Mihalcea because the object has reference to himself and the xmlPeriod are on the last level.

From that post I can't understand how to request the full tree of childs. I can use the code below to request the 1st level, but how to request N?

    private Set<XmlObject> getXOToProcessVM() {

        List<XmlObject> xmlObjects = entityManager.createQuery(
                        " select distinct p "  
                                " from XmlObject p "  
                                " left join fetch p.childObjects "  
                                " where p.id between :minId and :maxId", XmlObject.class)
                .setParameter("minId", 1L)
                .setParameter("maxId", 50L)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getResultList();

        xmlObjects = entityManager.createQuery(
                        " select distinct p "  
                                " from XmlObject p "  
                                " left join fetch p.childObjects "  
                                " where p in :xmlObjects", XmlObject.class)
                .setParameter("xmlObjects", xmlObjects)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getResultList();

        return Set.copyOf(xmlObjects);
        
    }

Besides of other tries, I've tried this Native Query in Repository, that seemed to be what I really need from Postgres perspective

    @Query(value = "select DISTINCT * from xml_object message "  
            " left join xml_object_tree tree1 on message.id = tree1.parent_id "  
            " left join xml_object sender on sender.id = tree1.child_id "  
            " left join xml_object_tree tree2 on sender.id = tree2.parent_id "  
            " left join xml_object area on area.id = tree2.child_id "  
            " left join xml_object_tree tree3 on area.id = tree3.parent_id "  
            " left join xml_object measuringPoint on measuringPoint.id = tree3.child_id "  
            " left join xml_object_tree tree4 on measuringPoint.id = tree4.parent_id "  
            " left join xml_object measuringChannel on measuringChannel.id = tree4.child_id "  
            " left join xml_period period on period.xml_object_id = measuringChannel.id "  
            " where message.id IN :ids", nativeQuery = true)
    Set<XmlObject> findAllObjectsByIdsFetchChildsAndPeriods(Iterable<Long> ids);

But it returns several rows for each object cause of joins, which is unacceptable.

I've tried this Query from Hibernate perspective, but it fetch only first level of childs, but I need all tree plus XmlPeriods from the last level. Can't use several JOIN FETCH cause of Cartesian Product(Set) and JPA restrictions(List)

    @Query(value = "select xo from XmlObject xo "  
            " join fetch xo.childObjects senders "  
            " where xo.id IN :ids")
    List<XmlObject> findAllObjectsByIdsFetchingChilds(Iterable<Long> ids);

CodePudding user response：

You will need a recursive CTE to model this, and since Hibernate has no support for a recursive fetch join, you will also have to build the object graph yourself.

The idea is to write a HQL query like this:

with nodes as (
    select :rootId as id, null as parentId
    from (values (1)) t(x)

    union all

    select c.id as id, xo.id as parentId
    from XmlObject xo 
    join nodes n on xo.id = n.id 
    join xo.childObjects c
)
select o, n.parentId
from nodes n
join XmlObject o on o.id = n.id
left join fetch o.xmlPeriods

You will need Hibernate 6.2 for that or Blaze-Persistence.

The result is a list of all nodes, you will then only have to wire them up correctly