HQL Hibernate query search check if list contains all elements of another list

I came across a problem that I cannot think of a solution. So I got this piece of code:

public List<Post> getPosts(List<PostStrategy> allowedStrategyList, Set<Tag> allowedTags, int page, int resultsPerPage) {
    return entityManager.createQuery("select post from Post post join post.tags tag where post.postStrategy in (:postStrategyList) and (:allowedTagsSize = 0 or tag in (:allowedTags))", Post.class)
            .setParameter("postStrategyList", allowedStrategyList)
            .setParameter("allowedTags", allowedTags)
            .setParameter("allowedTagsSize", allowedTags.size())
            .setFirstResult((page - 1) * resultsPerPage)
            .setMaxResults(resultsPerPage)
            .getResultList();
}

The problem with this piece of code is that when someone searches with more than one tag (for example: #video, #image), it returns both posts with two tags and one tag.

I would like it to return a post with both #video, and #image in its tags. For it to work, I somehow need to check if the list contains all elements of another list.

I searched for a solution for a while and tried different approaches so far, nothing.

I tried replacing “tag in (:allowedTags)” with “post.tags in (:allowedTags)” but that throws that my SQL is invalid.

Answer

Two years later I got my answer. I am now using Spring Boot and SpEL expressions so bear with me. This query does a little more than just my original idea but you can extract what you need from it.

@Override
@Query("select p from PostSnapshot p where " +
        // Tags
        "(:#{#query.withTags.size()} = 0 or p.id in (" +
        "select post.id from PostSnapshot post inner join post.tags tag " +
        "where tag.value in (:#{#query.withTags}) " +
        "group by post.id " +
        "having count(distinct tag.value) = :#{#query.withTags.size() * 1L}" +
        ")) and (:#{#query.withoutTags.size()} = 0 or p.id not in (" +
        "select post.id from PostSnapshot post inner join post.tags tag " +
        "where tag.value in (:#{#query.withoutTags}) " +
        "group by post.id " +
        "having count(distinct tag.value) = :#{#query.withoutTags.size() * 1L}" +
        ")) " +
        // Artists
        "and (:#{#query.withArtists.size()} = 0 or p.id in (" +
        "select post.id from PostSnapshot post inner join post.artists artist " +
        "where artist.preferredNickname in (:#{#query.withArtists}) " +
        "group by post.id " +
        "having count(distinct artist.preferredNickname) = :#{#query.withArtists.size() * 1L}" +
        ")) and (:#{#query.withoutArtists.size()} = 0 or p.id not in (" +
        "select post.id from PostSnapshot post inner join post.artists artist " +
        "where artist.preferredNickname in (:#{#query.withoutArtists}) " +
        "group by post.id " +
        "having count(distinct artist.preferredNickname) = :#{#query.withoutArtists.size() * 1L}" +
        ")) " +
        // Title like words
        "and lower(p.title) like concat('%', lower(:#{(#query.words.isEmpty()) ? '' : #query.words.toArray()[0]}) ,'%')" +
        // OwnerId
        "and p.ownerId = :ownerId")
Page<PostSnapshot> findAllByOwnerIdAndQuery(@Param("ownerId") UUID ownerId, @Param("query") PostQuerySearchDTO query, Pageable pageable);