FETCH JOIN maximum depth?

Question

W was trying to fetch join over three levels:

JOIN FETCH entity1.collection1.collection2  // two OneToMany relations

but got:

org.hibernate.HibernateException: Errors in named queries: [...]

Is it because it was too deep, or because a collection of collections cannot be fetched this way? My max fetch depth is 3, if this is relevant.

I can, at the same time, do a triple JOIN FETCH starting from the other side:

JOIN FETCH entity3.entity2.entity1  // two ManyToOne relations

Somehow I cannot find anything in JPA specification, or in Hibernate docs, that would limit the depth of this clause.

Answer 1

collection1 is of type Collection. And a Collection doesn't have a collection2 field. That's how I reason about those kind of queries.

You must the create an explicit join over the collection:

select e from Entity1 e
left join fetch e.collection1 as c1
left join fetch c1.collection2 as c2

Note that this will produce a cartesian product, and thus petentially returns a huge number of rows. Also note that it will only be possible if one of the two collections at least is a set. If they're both bags, Hibernate will throw an exception when executing the query.