This guide shows three effective strategies to fetch Theaters with their Shows, grouped and filtered by date, while preserving pagination and avoiding LAZY/EAGER pitfalls.

1. 🔄 Multi-step Approach Using ID Paging + Explicit Show Query

✅ Step 1: Paginate Theaters Having Shows in Date Range

@Query("""
    SELECT DISTINCT t.id FROM Theater t
    JOIN t.shows s
    WHERE s.showStartsAt BETWEEN :start AND :end
""")
Page<Long> findTheaterIdsWithShowsInRange(
    @Param("start") Instant start,
    @Param("end") Instant end,
    Pageable pageable
);

✅ Step 2: Fetch Only Relevant Shows per Theater

@Query("""
    SELECT s FROM Show s
    JOIN FETCH s.theater t
    WHERE t.id IN :theaterIds
    AND s.showStartsAt BETWEEN :start AND :end
""")
List<Show> findShowsByTheaterIdsAndTimeRange(
    @Param("theaterIds") List<Long> theaterIds,
    @Param("start") Instant start,
    @Param("end") Instant end
);

💻 Service Layer

Page<Long> theaterIdPage = theaterRepo.findTheaterIdsWithShowsInRange(start, end, pageable);
List<Show> shows = showRepo.findShowsByTheaterIdsAndTimeRange(theaterIdPage.getContent(), start, end);

Map<Long, List<Show>> groupedShows = shows.stream()
    .collect(Collectors.groupingBy(show -> show.getTheater().getId()));

List<TheaterShowsDTO> result = theaterIdPage.getContent().stream()
    .map(theaterId -> {
        List<Show> theaterShows = groupedShows.getOrDefault(theaterId, List.of());
        Theater theater = theaterShows.isEmpty() ? null : theaterShows.get(0).getTheater();
        return new TheaterShowsDTO(theaterId, theater != null ? theater.getName() : "Unknown", theaterShows);
    })
    .toList();

return new PageImpl<>(result, pageable, theaterIdPage.getTotalElements());

2. ⚙️ Dynamic Filtering Using JPA Specifications

🔧 Specification Class

public static Specification<Theater> hasShowsInRange(Instant start, Instant end) {
    return (root, query, cb) -> {
        query.distinct(true);
        Join<Theater, Show> showJoin = root.join("shows");
        return cb.between(showJoin.get("showStartsAt"), start, end);
    };
}

📞 Repository Call

Page<Theater> pagedTheaters = theaterRepo.findAll(hasShowsInRange(start, end), pageable);

⚠️ Note: You still need to filter shows manually post-fetch. JOIN FETCH doesn't work with pagination.

3. 📦 Lightweight Fetching via Projections (DTO)

🧾 DTO Example

public record TheaterShowProjection(
    Long theaterId,
    String theaterName,
    Long showId,
    Instant showStartsAt,
    Instant showEndsAt
) {}