I have the following entity and repository in my Spring JPA application (simplified for the sake of this example):

@Entity
class User {
    @Id
    val id: UUID

    @OneToOne
    val info: UserInfo

    @OneToMany
    val documents: List<Document>

    @OneToMany
    val posts: List<Post>
}

interface UserRepository : JpaRepository<User, UUID> {

    @Query("custom query defined here")
    fun findAllWithCustomQuery(): List<User>

}

After calling the method findAllWithCustomQuery I faced the N+1 problem. So I tried to create an EntityGraph to solve this problem. First I tried to create it only for the UserInfo relationship, and it worked fine (no extra query was executed to fetch UserInfo).

@Entity
@NamedEntityGraph(
    name = "graph.User", // I referenced this name in the repository method
    attributeNodes = [
        NamedAttributeNode("userInfo")
    ]
)
class User {
    @Id
    val id: UUID

    @OneToOne
    val info: UserInfo

    @OneToMany
    val documents: List<Document>

    @OneToMany
    val posts: List<Post>
}

But then I tried to apply the same approach for the documents and posts

@Entity
@NamedEntityGraph(
    name = "graph.User", // I referenced this name in the repository method
    attributeNodes = [
        NamedAttributeNode("userInfo"),
        NamedAttributeNode("documents"),
        NamedAttributeNode("posts")
    ]
)
class User {
    @Id
    val id: UUID

    @OneToOne
    val info: UserInfo

    @OneToMany(fetch = FetchType.EAGER)
    val documents: List<Document>

    @OneToMany(fetch = FetchType.EAGER)
    val posts: List<Post>
}

And it threw an exception with the error cannot simultaneously fetch multiple bags. I searched online and one of the solutions suggested using Set instead of List. I’m not sure if that would be ideal as it would generate a cartesian product, and in my real example I have five @OneToManyrelationships, which could lead to even worse performance issues. Besides that, I cannot find or think of another solution to this problem as it seems not feasible from a query perspective.

Is there a way to fetch all the information that I need (multiple users and their related documents and posts) in a single query? If not, is there a way to predict if the cartesian product would indeed be a problem or not?