Hibernate: Many to Many contains specific IDs using Criteria

Question

I'm developing a JPA application where I've reached a problem with Many To Many association between these entities:

Movie

class Movie {
    @Id 
    String id;

    ...

    @ManyToMany
    @JoinTable(
        name = "movie_category",
        joinColumn = @JoinColumn(name = "movie_id", referencedColumnName = "id")
    )
    Set<Category> categories = new HashSet<>();
}

Category

class Category {

    @Id
    String id;
    ...
}

I want to select only the movies having all the categories (List<Category> for instance). In Criteria API, I could do something like this:

List<Category> requiredCategories = ... //from request


CriteriaBuilder b = em.getCriteriaBuilder();
CriteriaQuery<Movie> cq = b.createQuery(Movie.class);
Root<Movie> r = cq.from(Movie.class);

Expression<Set<Category>> categories = r.get(Movie_.categories);

Predicate predicate = b.and();
for(Category c : requiredCategories) {
    predicate = b.and(predicate, b.isMember(c, categories));
}

Which works fine, but the problem is that I have to fetch Category objects from Category table first and then use it in isMember function. I would like to do it somehow avoiding joining Category table.

For example: I want all movies having category COMEDY and FANTASY. (consider these keywords being Category.id), so I need to pull by id from Category table both entities and then use them in isMember function. I want to avoid that because the information category_id is already stored in the association table movie_category. In short, I'd like something like isMember but providing an Category.id instead of the entity.

Is something like this possible?

Answer 1

To my best knowledge such a query is impossible using JPA builtin tools since join table is not directly accessible. But this is possible with FluentJPA:

public List<Movie> getMoviesByCategories(List<String> categoryIds) {
    int matchTotal = categoryIds.size();

    FluentQuery query = FluentJPA.SQL((Movie movie,
                                       JoinTable<Movie, Category> movieCategory) -> {

        discardSQL(movieCategory.join(movie, Movie::getCategories));

        List<String> movieIds = subQuery(() -> {
            String movieId = movieCategory.getJoined().getId();
            String catId = movieCategory.getInverseJoined().getId();

            SELECT(movieId);
            FROM(movieCategory);
            WHERE(categoryIds.contains(catId));
            GROUP(BY(movieId));
            HAVING(COUNT(movieId) == matchTotal); // COUNT(DISTINCT(movieId));
        });

        SELECT(movie);
        FROM(movie);
        WHERE(movieIds.contains(movie.getId()));

    });

    return query.createQuery(em, Movie.class).getResultList();
}

which produces the following SQL:

SELECT t0.* 
FROM t0 
WHERE (t0.id IN (SELECT t1.movie_id 
FROM movie_category t1 
WHERE (t1.CATEGORIES_id IN ?1 ) 
GROUP BY  t1.movie_id  
HAVING (COUNT(t1.movie_id) = ?2) ) )

The sub query is required to produce and semantics. See this answer. Many to many semantics in FluentJPA is explained here.