Convert SQL query that use ManyToMany relationship to JPQL query

Question

I'm trying to convert the following SQL query into JPQL query:

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

The problem is that i don't have a model class to represent movie_genre table because it's auto generated table from ManyToMany relationship.

so is there any way to convert that query into JPQL

In the moment I'm using a native query instead:

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

Edit : here's the models:
Movie

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

Genre

@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

Despite the SQL query you provided, I re-thought your requirement and translated that to a JPQL. So far what I understood you were finding related movies to a movie having common genres. If this is correct, you can achieve this by

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

This will generate the SQL as below

 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

Querying Collection Association in JPQL

When you are using JPQL, you are in the Object Relationship world. So when you are querying for an association that is a collection, you access them through that collection field. And when it is a ManyToMany association, so you are not having a join table that does have a mapped entity, you need to Join with the collection field. And JPA vendor automatically translates that to join with the join table.

Like if you are querying movies of certain genres, this will go

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

Performance Concern

As you notice in the generated SQL, there are many levels of join to fetch and filter the data. This leads to a performance bottleneck. To overcome this, you can have entity for the movie_genre table.

This scenario is fairly discussed here

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

Answer 2

You do not need a model movie_genre in order to write a JPQL statement. Hibernate knows about it implicitly when doing a JPQL statement.

Example:

SELECT m from Movie m left join m.genres g where g.name = 'Action'

The bidirectional "m.genres g" works with all bidirectional JPQL statements, including many-to-many where the association model is implicit and not actually present.

Example Code:

@Entity
@Table(name = "MOVIE")
public class Movie {

@Id
@GeneratedValue
private Long id;

@Column
private String name;

@ManyToMany(cascade = {CascadeType.ALL})
@JoinTable(name = "movie_genre",
        joinColumns = @JoinColumn(name = "movie_id"),
        inverseJoinColumns = {@JoinColumn(name = "genre_id")}
)
private Set<Genre> genres = new HashSet<>();

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}


public Set<Genre> getGenres() {
    return genres;
}

public void setGenres(Set<Genre> genres) {
    this.genres = genres;
}
}

@Entity
@Table(name = "GENRE")
public class Genre {

@Id
@GeneratedValue
private Long id;

@Column
private String name;


@ManyToMany(mappedBy = "genres", cascade = {CascadeType.ALL})
private Set<Movie> movies = new HashSet<>();

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public Set<Movie> getMovies() {
    return movies;
}

public void setMovies(Set<Movie> movies) {
    this.movies = movies;
}
}

Working JPQL Example

    @PersistenceContext
EntityManager entityManager;

@Test
@Transactional
public void test() {
    Set<Movie> actionMovies = new HashSet<>();
    Set<Movie> dramaMovies = new HashSet<>();
    Set<Genre> dramaGenres = new HashSet<>();
    Set<Genre> actionGenres = new HashSet<>();
    Set<Genre> generes = new HashSet<>();

    Movie actionMovie = new Movie();
    actionMovie.setName("Batman");
    actionMovies.add(actionMovie);

    Movie dramaMovie = new Movie();
    dramaMovie.setName("Forest Gump");
    dramaMovies.add(dramaMovie);

    Genre actionGenre = new Genre();
    actionGenre.setName("Action");
    generes.add(actionGenre);
    actionGenres.add(actionGenre);

    Genre dramaGenre = new Genre();
    dramaGenre.setName("Drama");
    generes.add(dramaGenre);
    dramaGenres.add(dramaGenre);

    //Bidirectional sets
    actionGenre.setMovies(actionMovies);
    dramaGenre.setMovies(dramaMovies);
    actionMovie.setGenres(actionGenres);
    dramaMovie.setGenres(dramaGenres);

    genreRepository.saveAll(generes);

    //Example JPQL join through not present association model.
    Query query = entityManager.createQuery("SELECT m from Movie m left join m.genres g where g.name = 'Action'");
    List<Movie> resultList = query.getResultList();
    assertEquals("Batman",resultList.get(0).getName());


}