0

The administrative locations in my country are organized in the following precedence order(largest to smallest):

Country > Region > District > Council > locality.

All entities are well established with one-to-many mapping between parents and children entities

I want to get the set of localities with their corresponding councils and Districts and Regions with the following named query;

@NamedQuery(name = "LocalityWithCouncilAndDistrictAndRegionByLocalityId",
    query = "SELECT DISTINCT lc " +
            "FROM Locality lc " +
            "LEFT JOIN FETCH lc.council c " +
            "LEFT JOIN FETCH c.district dst " +
            "LEFT JOIN FETCH dst.region  " +
            "WHERE lc.localityId = :localityId")

But the validation fails with an error saying

JOIN FETCH expressions cannot be defined with an identification variable

The error highlights on these two lines below from the query;

        "LEFT JOIN FETCH lc.council c " +
        "LEFT JOIN FETCH c.district dst " +

Please kindly help me on the best approach to resolving this.

geobudex
  • 536
  • 1
  • 8
  • 24
  • Some JPA providers do (EclipseLink) allow this, but JPA does not require nested fetch joins to be supported in JPQL. I think the solution used elsewhere is to define a load/fetch graph - hibernate treats anything eagerly loaded as fetch join anyway: https://www.baeldung.com/jpa-entity-graph – Chris Jan 24 '22 at 19:39
  • Hi Chris, I recall to have being using these types of join fetch with Hibernate without raising any issue. Also, I read articles like https://vladmihalcea.com/hibernate-facts-multi-level-fetching/ that deal with multi level fetching in similar fashion. Additionally, answers to similar question at https://stackoverflow.com/questions/30088649/how-to-use-multiple-join-fetch-in-one-jpql-query seems to adopt nested fetch joins. Maybe there are some implementation variation. – geobudex Jan 24 '22 at 22:53
  • You'd have to check with what ever is processing your JPQL string (show the stack), but JPA does not allow having identification variables on join fetches. It doesn't allow it for good reason though, as many then try to filter on it which (IMO) is something to be avoided as the model returned won't reflect what is in the DB. For the links you posted, they are all about why fetch joins (automatic in eager relationship) cause problems. I prefer using batch fetching in many models on certain relationships. – Chris Jan 25 '22 at 16:07

0 Answers0