This guide walks you through how to paginate shows grouped by theaters, based on filters like movie ID, screen type, and show time range. This is a common real-world use case for ticket-booking platforms (like BookMyShow).

✅ Objective

Given inputs: page, size, movieId, screenType, fromTime, toTime Return: Paginated list of theaters, each with all matching shows grouped under them.

🏗️ Step 1: Paginate Theater IDs With Matching Shows

We only want theaters that have shows matching the filters. To paginate at the theater level, we fetch distinct theater IDs.

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

📌 This returns a Page<Long> of theater IDs — paginated as requested.

🧾 Step 2: Fetch Required Show + Screen + Theater Info Using Projection

We use a projection to avoid fetching full entities. This is more efficient and avoids lazy-loading pitfalls.

✅ Projection Interface:

public interface TheaterShowProjection {
    Long getTheaterId();
    String getTheaterName();
    String getLandmark();
    String getCity();
    Long getScreenId();
    ScreenType getScreenType();
    Long getShowId();
    Instant getShowStartsAt();
    Instant getShowEndsAt();
    BigDecimal getPrice();
}

📥 Projection Query:

@Query("""
    SELECT new com.example.dto.TheaterShowProjectionImpl(
        t.id, t.name, t.landmark, t.city,
        sc.id, sc.screenType,
        s.id, s.showStartsAt, s.showEndsAt, s.price
    )
    FROM Show s
    JOIN s.theater t
    JOIN s.screen sc
    WHERE t.id IN :theaterIds
      AND s.movie.id = :movieId
      AND s.showStartsAt BETWEEN :start AND :end
      AND sc.screenType = :screenType
""")
List<TheaterShowProjection> fetchProjectedShowsByTheaterIds(
    @Param("theaterIds") List<Long> theaterIds,
    @Param("movieId") Long movieId,
    @Param("start") Instant start,
    @Param("end") Instant end,
    @Param("screenType") ScreenType screenType
);

🔄 Step 3: Group Results by Theater and Construct Final Output

Once you get a flat list of projections, group them by theater ID:

Map<Long, List<TheaterShowProjection>> grouped = projections.stream()
    .collect(Collectors.groupingBy(TheaterShowProjection::getTheaterId));

List<TheaterShowsDTO> finalResult = grouped.entrySet().stream()
    .map(entry -> new TheaterShowsDTO(
        entry.getKey(),
        entry.getValue().get(0).getTheaterName(),
        entry.getValue().get(0).getCity(),
        entry.getValue()
    ))
    .toList();

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