Querying hierarchical data with JPQL / HQL

Question

We are trying to implement a simple model of hierarchical data, which is as follows.

public class Team {
    /* ... */

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "team")
    protected List<Member> members;
}

public class Member {
    /* ... */

    @ManyToOne(fetch = FetchType.LAZY)
    protected Team team;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "member")
    protected List<Assignment> assignments;
}

public class Assigment {
    /* ... */

    @ManyToOne(fetch = FetchType.LAZY)
    protected Member member;
}

The idea behind the mapping is quite straightforward - a team is composed of members, who are in turn assigned some duties.

Problem: creating a query which will return the hierarchy of team->member->assignment with conditions imposed on assignments (e.g. deadline in < 1 week).

Solutions so far:

1. Querying for all Team entities and iterating over two-level collections in a transaction, filtering out those assignments, which do not fall into time range. Pros: simple. Cons: possible large overhead.

2. Following query:

   SELECT t FROM Team t
     left join fetch t.members m
     left join fetch m.assignments
   

   Unfortunately, the query fails even without the any condition for assigment filtering, throwing:

   org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags
   

I don't understand why it does not allow simultaneous fetching on two relations, as a similar example is shown in Hibernate docs:

from Cat as cat
  inner join fetch cat.mate
  left join fetch cat.kittens child
  left join fetch child.kittens

Questions:

1. Is it possible to fetch the whole tree-like collection with filtered leaf-level (assignments) with a single SQL statement? If not, what could be improved in the proposed solution?
2. Why the double fetch does not work properly?

Additional info: we're using Hibernate 4.3.5.FINAL & PostgreSQL.

Answer 1

The double fetch means a Cartesian Product, So if you have 100 teams with 100 members and 100 assignments you will get 100 x 100 X 100 = 1,000,000 results.

The best way of fetching is from the bottom-up.

SELECT a FROM Assigment a
  inner join fetch a.member m
  inner join fetch m.team t

and reconstruct the tree from the bottom-up. You can use the EntityGraphBuilder to map it in Java.

Answer 2

Is it possible to fetch the whole tree-like collection with filtered leaf-level (assignments) with a single SQL statement? If not, what could be improved in the proposed solution?

Yes, it's possible. But, you need to change your mapping to use Set instead of List.

Why the double fetch does not work properly?

When you try to fetch multiple collections you cannot have two or more List in the same query.

So, change the assignments or members from List to Set.

To filter the assigments you cannot do that in a fetched clause. If you want to fetch all collections and do a filter in assigment, your query could be:

SELECT t FROM Team t
  join fetch t.members m -- only for fetch
  join fetch m.assignments -- only for fetch
  join t.members member -- new join to do the where
  join member.assigments assigment -- new join to do the where
  where assigment.anyAttribute = someValue -- filter assigment

But, as @Vlad said, the cartesian product can be huge with this solution and bring a lot of unecessary information in the SQL result.