[FIXED] Why JPA Inheritance with EntityGraph queries twice a related entity?

Issue

I have mapped through JPA the following entities:

@Entity(name = "Parent")
@Table(name = "parent")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "type")
public abstract class Parent {

    @Id
    @GenericGenerator(name = "CustomUUIDGenerator", strategy = "package.CustomUUIDGenerator")
    @GeneratedValue(generator = "CustomUUIDGenerator", strategy = GenerationType.AUTO)
    private UUID id;

    @ManyToMany
    @Fetch(FetchMode.JOIN)
    @JoinTable(name = "parent_country", joinColumns = @JoinColumn(name = "parent_id"),
            inverseJoinColumns = @JoinColumn(name = "country_id"))
    private Set<Country> countries;

}

@Entity(name = "ChildA")
@Table(name = "child_a")
@DiscriminatorValue(value = "CHILD_A")
public class ChildA extends Parent {

    private String description;

}

This reflects to the following Postgres' table schema:

Table PARENT columns: ID, TYPE

Table COUNTRY columns: ID, CODE, LANGUAGE

Table PARENT_COUNTRY columns: PARENT_ID, COUNTRY_ID

Table CHILD_A columns: ID, DESCRIPTION

Based on that I'm trying to create an CriteriaQuery using EntityGraph to control the dynamic associated entities loading. The following code presents how the given query:

EntityGraph<ChildA> entityGraph = entityManager.createEntityGraph(ChildA.class);
entityGraph.addAttributeNodes(Parent_.COUNTRIES);

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ChildA> criteriaQuery = criteriaBuilder.createQuery(ChildA.class);
Root<ChildA> root = criteriaQuery.from(ChildA.class);
List<Predicate> predicates = new ArrayList<>();
final Join<ChildA, Country> join = root.join(ChildA_.COUNTRIES, JoinType.LEFT);
predicates.add(criteriaBuilder.equal(join.get(Country_.CODE), "USA"));
criteriaQuery.where(predicates.toArray(Predicate[]::new));

TypedQuery<ChildA> finalQuery = entityManager
    .createQuery(criteriaQuery)
    .setHint(EntityGraphPersistence.LOAD_GRAPH, entityGraph);

List result = finalQuery.getResultList();

The issue is that, when I run the query above I get the following SQL printed on the console:

select
    * -- All hibernate fields from all entities
from 
    child_a ca
inner join
    parent pa
        on ca.id=pa.id
left outer join
    parent_country pc
        on pc.parent_id = ca.id
left outer join
    country co
        on pc.country_id = co.id
left outer join
    parent_country pc_2
        on pc2.parent_id = ca.id
left outer join
    country co2
        on pc2.country_id = co2.id
where
(
    co.code=?
)

The issue from the query above is the double join with PARENT_COUNTRY and COUNTRY. In this query I'm filtering the results based on the country code (equal to USA), so I don't want to get back from the database other countries (different from USA). However, due to the second left join to country, the parent is related again to countries and the filter done on the first relation to co.code=? is ignored.

Does anybody know how to prevent the double join in this use case?

Solution

Finally after struggling a whole day with this problem I found the solution. Turns out that the solution is to use a feature called fetch-join from JPA, it allows you to join a relation and use it to fetch the data that will be used to populate the entity.

However a few syntax tweaks had to be made to make it work. Also when I fetch-join a relation using criteria builder, I should also ignore them from the EntityGraph so I don't join the same entity twice. The snippet below shows how it works:

EntityGraph<ChildA> entityGraph = entityManager.createEntityGraph(ChildA.class);
//entityGraph.addAttributeNodes(Parent_.COUNTRIES);

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ChildA> criteriaQuery = criteriaBuilder.createQuery(ChildA.class);
Root<ChildA> root = criteriaQuery.from(ChildA.class);
List<Predicate> predicates = new ArrayList<>();
//final Join<ChildA, Country> join = root.join(ChildA_.COUNTRIES, JoinType.LEFT);
Fetch<ChildA, Countries> fetch = root.fetch(ChildA_.COUNTRIES, JoinType.LEFT); // new line
Join<ChildA, Countries> join = (Join<ChildA, Countries>) fetch; // new line
predicates.add(criteriaBuilder.equal(join.get(Country_.CODE), "USA"));
criteriaQuery.where(predicates.toArray(Predicate[]::new));

TypedQuery<ChildA> finalQuery = entityManager
    .createQuery(criteriaQuery)
    .setHint(EntityGraphPersistence.LOAD_GRAPH, entityGraph);

List result = finalQuery.getResultList();

You can find more details about this topic at this link: https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/4

Answered By - João Pedro Schmitt
Answer Checked By - Marilyn (JavaFixing Volunteer)