Optimizing ManyToMany Relationship Fetches with DynamicEntityGraph to Prevent N + 1 Queries in Spring Data JPA

[alperkrtglu]

What steps will reproduce the problem?

1. Define entities with a ManyToMany relationship using @jointable, like in the AJpaEntity example provided.
2. Attempt to fetch an instance of AJpaEntity using findById with a DynamicEntityGraph that adds the path to the properties (which is the ManyToMany relationship).
3. Observe that the generated SQL first joins with the intermediary table and then performs additional queries to fetch BJpaEntity instances, leading to an N + 1 problem.

@Entity
class AJpaEntity(
    id: UUID,
    var name: String,
    properties: MutableList<BJpaEntity>,
) : BaseEntity(id) {

  @ManyToMany(cascade = [CascadeType.DETACH])
  @JoinTable(
      name = "a_and_b",
      joinColumns = [JoinColumn(name = "a_id")],
      inverseJoinColumns = [JoinColumn(name = "b_id")])
  var properties: MutableList<BJpaEntity> = properties
}

AJpaRepository
            .findById(
                id,
                DynamicEntityGraph.loading()
                    .addPath("properties")
                    .build())

What is the expected output?
A single query or a reduced number of queries that fetch AJpaEntity and the related BJpaEntity instances without causing an N + 1 problem, using the DynamicEntityGraph.

What happens instead?
The actual behavior is that the system performs a join with the intermediary table and then issues separate queries to fetch each BJpaEntity instance related to AJpaEntity, resulting in an N + 1 query issue.

Environment

1. Spring Data JPA version 3.2.2
2. ORM with version: 6.4.0
3. spring-data-jpa-entity-graph version: 3.2.2
   Link to an automated test demonstrating the problem

Additional context
In the current setup, attempting to dynamically include a ManyToMany relationship in a single fetch operation using DynamicEntityGraph leads to inefficient query patterns. A solution or workaround that effectively reduces the number of generated queries while maintaining the flexibility of dynamic entity graphs is sought.

[reda-alaoui]

The current project passes the EntityGraph to the ORM. What the ORM does with it is the responsibility of the orm.

[alperkrtglu]

However, when we use JPA's EntityGraph, it queries with a single SQL and fetches the data. In this case, I did not encounter the N + 1 problem.

@NamedEntityGraph(name = "Properties", 
                     attributeNodes = [NamedAttributeNode("properties")])

  @EntityGraph(value = "Properties") 
  override fun findById(id: UUID): Optional<AJpaEntity>

I believe this can also be done in your library. But what I am curious about here is whether we can use NamedEntityGraph and DynamicEntityGraph together? Do you have any recommendations?

[reda-alaoui]

Your second example uses named entity graph. Try with dynamic graph via the Spring Data @entitygraph annotation.
In any case, you must provide a reproduction case (code that just need to be cloned and runned).