JPA Criteria API - Subquery with join

Question

How can I write this JPQL query in JPA Criteria API ?

SELECT t FROM TvShow t
WHERE (
    SELECT COUNT(g)
    FROM Genre g
    WHERE t MEMBER OF g.tvShows
) <= 2

Answer 1

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<TvShow> criteriaQuery = criteriaBuilder.createQuery(TvShow.class);
    Root<Service> from = criteriaQuery.from(TvShow.class);
    CriteriaQuery<TvShow> select = criteriaQuery.select(from);

    Subquery<Long> subQueryGenre = select.subquery(Long.class);
    Root<Genre> fromGenre = subQueryGenre.from(Genre.class);
    subQueryGenre.select(criteriaBuilder.count(fromGenre));
    subQueryGenre.where(criteriaBuilder.equal(from, fromGenre.get("tvShows")));

    select.where(criteriaBuilder.lessThanOrEqualTo(subQueryGenre , 2));
    TypedQuery<TvShow> typedQuery = entityManager.createQuery(select);
    try {
        return typedQuery.getResultList();
    } catch (Exception ex) {
        return = new ArrayList<TvShow>();
    }