Issue using @Query to fetch lazy loaded association in JPA

Question

So I have an entity Game which contains

    @ElementCollection
    private List<String> moves;

I only require this field for one specific call so the default behavior of Lazy fetching is the best choice. I don't have much experience with lazy loading so I looked around and it seems like the two preferred choices for implementing lazy loading are @EntityGraph and @Query. I looked into both and while I don't have a great understanding for the best use cases for each, I decided to use @Query.

public interface GameRepository extends CrudRepository<Game, Long> {
    @Query("SELECT g FROM Game g JOIN FETCH g.moves WHERE g.id = :id")
    Optional<Game> findByIdWithMoves(@Param("id") Long id);
}

Seems simple and straight forward but this method does not find anything. The regular findById method returns the Game but my custom query does not find anything. I've looked up other examples like this one How to fetch FetchType.LAZY associations with JPA and Hibernate in a Spring Controller and made minor changes like changing long to Long and tried the query without the Optional container so that it fits exactly but its still not working and I'm stuck.

Answer 1

The problem arose from the fact that List<String> Moves is empty for the initial database fetch. So even though moves had been initialized, there was nothing for Game to join to and the standard JOIN will not return items without the specified association.

What I needed was a LEFT JOIN because they always returns all rows of the left side (Game) entity.

@Query("SELECT g FROM Game g LEFT JOIN FETCH g.moves WHERE g.id = :id")