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.
Advertisement
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);