How to fetch a OneToMany relation between two entities that are both ManyToOne-related to the root entity

Question

I have a typical N+1-problem. In my scenario, there are multiple OneToMany relations, and I solved the N+1-problem for each of them, except for one. These are my entities:

@Data
@Entity
@NoArgsConstructor
public class Person {

    @Id
    @GeneratedValue
    @Column(nullable = false, updatable = false)
    private Long id;

    @OneToMany(mappedBy = "person")
    @JsonManagedReference
    @ToString.Exclude
    private List<Foot> feet;

    @OneToMany(mappedBy = "person")
    @JsonManagedReference
    @ToString.Exclude
    private List<Hand> hands;
}

@Data
@Entity
@NoArgsConstructor
public class Foot {

    @Id
    @GeneratedValue
    @Column(nullable = false, updatable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(nullable = false, updatable = false)
    @JsonBackReference
    private Person person;

    @OneToMany(mappedBy = "foot")
    @JsonManagedReference
    @ToString.Exclude
    private List<Hand> hands;
}

@Data
@Entity
@NoArgsConstructor
public class Hand {

    @Id
    @GeneratedValue
    @Column(nullable = false, updatable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(nullable = false, updatable = false)
    @JsonBackReference
    private Person person;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(nullable = true, updatable = false)
    @JsonBackReference
    private Foot foot;
}

So basically I have a Person that has an arbitrary number of feet and hands. In addition to that, a foot also has an arbitrary number of hands.

I was able to solve the N+1-problem for both the Person-Foot and Person-Hand relation by using two JOIN FETCH queries:

List<Person> personList = repository.getPersonsFetchFeet();
repository.getPersonsFetchHands();
System.out.println(new ObjectMapper().writeValueAsString(personList));

With the repository methods:

    @Query("SELECT p FROM Person p JOIN FETCH p.feet")
    List<Person> getPersonsFetchFeet();

    @Query("SELECT p FROM Person p JOIN FETCH p.hands")
    List<Person> getPersonsFetchHands();

The N+1-problem still exists for the Foot-Hand relation. So I added another repository method:

    @Query("SELECT f FROM Foot f JOIN FETCH f.hands")
    List<Foot> getFeetFetchHands();

And I run it right after running the other two repository methods:

List<Person> personList = repository.getPersonsFetchFeet();
repository.getPersonsFetchHands();
repository.getFeetFetchHands();
System.out.println(new ObjectMapper().writeValueAsString(personList));

However, the SQL log still shows that there are many queries fetching the Hand entities based on foot_id. This is the sql log, last line will be logged thousands of times:

select p1_0.id,f1_0.person_id,f1_0.id from Person p1_0 join Foot f1_0 on p1_0.id=f1_0.person_id
select p1_0.id,h1_0.person_id,h1_0.id,h1_0.foot_id from Person p1_0 join Hand h1_0 on p1_0.id=h1_0.person_id
select f1_0.id,h1_0.foot_id,h1_0.id,h1_0.person_id,f1_0.person_id from Foot f1_0 join Hand h1_0 on f1_0.id=h1_0.foot_id
select h1_0.foot_id,h1_0.id,h1_0.person_id from Hand h1_0 where h1_0.foot_id=?

In my understanding, after the 3rd query, all the information should be available.

How do I get rid of this N+1 problem?

I use Spring Boot 3.2.4 with Spring Data JPA and Postgres.

Answer 1

I found the solution by accident. The issue was that many feet didn't have any related hands. Due to this, they were excluded from the query SELECT f FROM Foot f JOIN FETCH f.hands. I didn't realize before because each Person had at least one related Foot and at least one related Hand. When I removed the Person-Foot relation for one Person, an additional query was run by the framework.

I changed all my queries to use LEFT JOIN FETCH instead of JOIN FETCH and now it works.