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

Question

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);

I

Does this answer your question? [Hibernate throws MultipleBagFetchException - cannot simultaneously fetch multiple bags](https://stackoverflow.com/questions/4334970/hibernate-throws-multiplebagfetchexception-cannot-simultaneously-fetch-multipl) — XtremeBaumer, Jan 25 '23 at 09:05
It is similar in some ways, but is different, because here I want to fetch tree structure of object that is parent and child simultaneously. — Alex A, Jan 25 '23 at 09:09
It doesn't matter how the structure looks exactly. The issue is about multiple `@OneToMany` with list type — XtremeBaumer, Jan 25 '23 at 09:16
Updated the post with my try of using Vlad's proposal. It does matter actually in my point of view. — Alex A, Jan 25 '23 at 09:36
When I have to store a tree un a DB, I usually add a relation to the root node of the tree on all nodes. This helps loading in the whole tree with a single request and do the rest of the processing in memory. Of course this is only possible if the number of nodes is reasonable. — Pierre Demeestere, Jan 25 '23 at 10:23
This is particularly usefull when you store several independent trees in the same table of the db. — Pierre Demeestere, Jan 25 '23 at 11:28
The overall process is something like this 1. Parse and save xml file 2. Create or update unique tree table's nodes and relations with data from xml table To fill in unique tree table I have to match trees from new incoming data with data already presented in the unique tree table. — Alex A, Jan 25 '23 at 11:30

score 0 · Accepted Answer · answered Feb 02 '23 at 18:40

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

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

1 Answers1