java - 将使用 ManyToMany 关系的 SQL 查询转换为 JPQL 查询

Question

我正在尝试将以下 SQL 查询转换为 JPQL 查询:

SELECT *
FROM movie m INNER JOIN movie_genre mg ON m.id = mg.movie_id
WHERE mg.genre_id = (SELECT mg2.genre_id FROM movie_genre mg2 WHERE mg2.movie_id = ?1 AND mg2.movie_id <> mg.movie_id AND mg.genre_id = mg2.genre_id)
GROUP BY mg.movie_id ORDER BY count(*) DESC

问题是我没有模型类来表示 movie_genre 表，因为它是根据 ManyToMany 关系自动生成的表。

有什么方法可以将该查询转换为JPQL

目前我正在使用 native 查询:

@Query(value = "SELECT * FROM movie m INNER JOIN movie_genre mg ON m.id = mg.movie_id " +
            "WHERE mg.genre_id = (SELECT mg2.genre_id FROM movie_genre mg2 WHERE mg2.movie_id = ?1 AND mg2.movie_id <> mg.movie_id AND mg.genre_id = mg2.genre_id) " +
            "GROUP BY mg.movie_id ORDER BY count(*) DESC", nativeQuery = true)
Page<Movie> findRelatedMoviesToAMovieById(@Param("id") int id, Pageable pageable);

编辑:这是模型:
电影

@Entity
public class Movie extends DateAudit  {

    private Long id;
    private String name;
    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
     @JoinTable(name = "movie_genre",
        joinColumns = @JoinColumn(name = "movie_id"),
        inverseJoinColumns = @JoinColumn(name = "genre_id")
    )
    private List<Genre> genres = new ArrayList<>();
}

类型

@Entity
public class Genre {

    private Long id;
    private String name;
    @ManyToMany(mappedBy = "genres", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Set<Movie> movies = new HashSet<>();
}

Answer 1

尽管您提供了 SQL 查询，我还是重新考虑了您的需求并将其转换为 JPQL。到目前为止，据我了解，您正在寻找与具有共同类型的电影相关的电影。如果这是正确的，您可以通过以下方式实现此目的

SELECT m FROM Movie m JOIN m.genres mg 
WHERE mg.id IN 
         (SELECT g.id FROM Genre g JOIN g.movies gm WHERE gm.id = :movieId) 
AND m.id <> :movieId 
GROUP BY m ORDER BY count(*) DESC

这将生成如下 SQL

 select distinct movie0_.id as id1_1_, movie0_.name as name2_1_ 
 from movie movie0_ inner join movie_genre genres1_ on movie0_.id=genres1_.movie_id inner join genre genre2_ on genres1_.genre_id=genre2_.id 
 where (genre2_.id in 
            (select genre3_.id from genre genre3_ inner join movie_genre movies4_ on genre3_.id=movies4_.genre_id inner join movie movie5_
             on  movies4_.movie_id=movie5_.id where movie5_.id=?)) 
 and movie0_.id <> ? 
 group by movie0_.id order by count(*) desc

在JPQL中查询集合关联

当您使用 JPQL 时，您就处于对象关系世界中。因此，当您查询作为集合的关联时，您可以通过该集合字段访问它们。当它是 ManyToMany 关联时，因此您没有包含映射实体的联接表，则需要使用集合字段进行联接。 JPA 供应商会自动将其转换为与连接表连接。

就像如果您正在查询某些类型的电影，这将会出现

 SELECT m FROM Movie m JOIN m.genres mg WHERE mg.id = :genreId 

性能问题

正如您在生成的 SQL 中注意到的，有许多级别的联接来获取和过滤数据。这会导致性能瓶颈。为了克服这个问题，您可以为 movie_genre 表提供实体。

这里对这种情况进行了充分的讨论

The best way to map a many-to-many association with extra columns when using JPA and Hibernate