将使用多对多关系的 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

目前我正在使用本机查询：

@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<>();

}