Eagerly load nested association using HQL

I have the following model:

public class BaseModel {
  List<DataA> lazyCollectionA;
  List<DataB> lazyCollectionB;
}

public class DataA {
  OtherEntity otherEntity;
}

public class OtherEntity {
  List<DataC> lazyCollectionC;
}

When I visit a particular page I need to use all this data. This is creating a performance select n+1 problem.

I already partly solved the issue by eagerly fetching the collections using:

List<BaseModel> result = entityManager.createQuery(
    "select m from BaseModel m " +
    "left join fetch m.lazyCollectionA " +
    "where m.id in (:ids) ", BaseModel.class)
.setParameter("ids", ids)
.getResultList();

result = entityManager.createQuery(
    "select m from BaseModel m " +
    "left join fetch m.lazyCollectionB " +
    "where m.id in (:ids) ", BaseModel.class)
.setParameter("ids", ids)
.getResultList();

Note that I had to perform 2 queries instead of only 1 because otherwise I would get a MultipleBagFetchException.

However, I'm having problems eagerly loading lazyCollectionA.otherEntity.lazyCollectionC. I tried several variations of the query to try to eagerly fetch the results, but when otherEntity.lazyCollectionC is accessed, the select n+1 problem keeps surfacing.

I think this should work, but unfortunately it is not:

entityManager.createQuery(
    "select a from BaseModel m " +
    "left join m.lazyCollectionA a " +
    "left join fetch a.otherEntity o " +
    "left join fetch o.lazyCollectionC " +
    "where m.id in (:ids) ", BaseModel.class)
.setParameter("ids", ids)
.getResultList();

Do you have any ideas why this is not working?

Also, I don't fully understand how my first 2 queries to load lazyCollectionA and lazyCollectionB are working. I mean, since they are loaded at different times, I would expect that only the last query would have the loaded instances. Is it because hibernate is caching the results and therefore it does not need to query the database again?

Thanks for any help you can provide!

I assume that all connections between your models are @OneToMany. In this case you could try simething like this:

@Autowired
private EntityManager em;

@Transactional
public List<BaseModel> getAllByThreeQueries() {
    List<Long> ids = Arrays.asList(1L);
    List<BaseModel> first = em.createQuery(
            "select distinct m from BaseModel m " +
                    "left join fetch m.lazyCollectionB " +
                    "where m.id in (:ids) ", BaseModel.class)
            .setParameter("ids", ids)
            .getResultList();
    List<BaseModel> second = em.createQuery(
            "select distinct m from BaseModel m " +
                    "left join fetch m.lazyCollectionA a " +
                    "left join fetch a.otherEntity o " +
                    "where m in (:models) ", BaseModel.class)
            .setParameter("models", first)
            .getResultList();
    em.createQuery("select distinct a from BaseModel m " +
            "left join m.lazyCollectionA a " +
            "left join fetch a.otherEntity o " +
            "left join fetch o.lazyCollectionC " +
            "where m in (:models) ", DataA.class)
            .setParameter("models", second)
            .getResultList();
    return second;
}

Full code

> Do you have any ideas why this is not working?
>

entityManager.createQuery(
    "select a from BaseModel m " +
    "left join m.lazyCollectionA a " +
    "left join fetch a.otherEntity o " +
    "left join fetch o.lazyCollectionC " +
    "where m.id in (:ids) ", BaseModel.class)
.setParameter("ids", ids)
.getResultList();

Because you get a MultipleBagFetchException in this case. You need to do one more request.