JPQL查询按平均评级排序，然后按电影票房值排序

Question

好的，我有两个JPA实体，一个电影和一个MovieRating。如下所示

@Entity
@Table(name = Movie.TABLE_NAME)
public class Movie {

    static final String TABLE_NAME = "Movies";

    @Id
    @Column(name = "IMDB_ID")
    private String imdbID;

    @Column(name = "BOX_OFFICE_TAKINGS")
    private int boxOfficeTakings;

    @OneToMany(mappedBy = "movie", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<MovieRating> ratings = new ArrayList<>();

    // Getters and setters

}

和

@Entity
@Table(name = MovieRating.TABLE_NAME)
public class MovieRating {

    static final String TABLE_NAME = "MovieRatings";

    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    private UUID id;

    private int rating;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "IMDB_ID")
    private Movie movie;

    //Getters and setters
}

我在JPA中写了一个@Query，它应该返回按平均评级降序的结果，然后按票房值下降，例如。

@Query("SELECT m FROM Movie m LEFT JOIN m.ratings r GROUP BY m ORDER BY AVG(r.rating) desc, m.boxOfficeTakings desc")
List<Movie> findTop10OrderedByBoxOfficeTakings(Pageable pageable);

例如，我希望一部票房值较低的电影出现在一部票房值较高的未评级电影之上。

所以我已经写了一个测试，它似乎像我所期望的那样起作用

@Test
public void testGetTop10ReturnsRatedMovieAboveUnratedMovieWithHigherValue() {
    Movie movie1 = createMovieWithRatings(new Movie("tt000001", "The Godfather part 1", 2010, 268500000), null);
    Movie movie2 = createMovieWithRatings(new Movie("tt000002", "The Godfather part 2", 2010, 93000000),
            Arrays.asList(new MovieRating(10)));
    
    movieRepository.saveAll(Arrays.asList(movie1, movie2));
    
    List<MovieResponse> top10 = movieService.getTop10OrderedByBoxOfficeTakings();
    
    List<String> expectedMovieIds = Arrays.asList(movie2.getImdbID(), movie1.getImdbID());

    List<String> actualMovieIds = top10.stream().map(movie -> movie.getImdbID()).collect(Collectors.toList());

    Assertions.assertEquals(expectedMovieIds, actualMovieIds);
}

private Movie createMovieWithRatings(Movie movie, List<MovieRating> ratings) {
    if (ratings != null) {
        ratings.forEach(rating -> movie.addRating(rating));
    }
    return movie;
}

然而，在实践中，当代码运行时，顺序是movie1，然后是movie2。只有当Movie2收到超过一个等级时，它才会在movie1之上移动。为什么？

Answer 1

也许是因为你在做一个左连接，如果没有值的话，你想要加入的任何东西，在你的例子中，“教父第1部分”没有任何评级，所以它是空的。尝试在查询本身中放置一个空检查，如果它是空替换为0，那么现在它可以计算每个电影的AVG。也许能帮上忙。