CODEWITHSUNDEEP
javaspringhibernatejpahql
Rahul
Rahul

Reputation: 1807

HQL checking if object contains all element s of requested set - duplicate

I have two entities. For example, posts and tags to it. I have to write a method that will take only posts, which have all of tags, mentioned in query.

I tried

@Query("select distinct p from posts p join p.tags t where t in ?1")
Page<Post> findDistinctByTagsIn(Set<Tag> tagSet, Pageable pageable);

However it takes Post if at least one of its tags included in tagSet.

How can I solve this using only HQL and JPA Repositories?

@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinTable(name = "posts_tags", joinColumns = {
        @JoinColumn(name = "post_id", nullable = false, updatable = false)},
        inverseJoinColumns = {@JoinColumn(name = "tag_id")})
public Set<Tag> getTags() {
    return tags;
}

Upvotes: 2

Views: 1202

Answers (2)

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23552

You have to group by posts and select only posts for which the count of matched records is equal to the count of the passed-in tags (meaning that all tags match):

select p from Posts p join p.tags t
where t in :tags
group by p
having count(p) = :tagsSize

However, you have to pass in additional tagsSize parameter with the value of tagSet.size().

You could change your repository method signature to accept this param and call the method like this:

repo.findDistinctByTagsIn(tagSet, tagSet.size(), Pageable pageable);

and, to avoid redundancy, change it later to using SpEL expression when Spring adds support for it:

@Query("select p from Posts p join p.tags t where t in :tags "
     + "group by p having count(p) = :#{#tags.size()}")
Page<Post> findDistinctByTagsIn(@Param("tags") Set<Tag> tagSet, Pageable pageable);

Or, as the query is getting a bit more complex anyway, you can create it directly with the EntityManager in a separate method and set the desired parameters manually, so that you don't force the clients of the method to be aware of the query semantics details.

Upvotes: 2

Malli
Malli

Reputation: 1

There should be primary key for TAGS table.

we can pass primary key's set of tags object to NamedParameter query then you get only Post object which are mapped to Tag's object.

you have to write query with respect to Many to Many object.

example : query = SELECT mm.post FROM POST_TAG_MM mm WHERE mm.tag.primarykeycolumn in (:postPrimaryKeySet);

Query q= s.getNamedQuery(query); q.setParameterList("postPrimaryKeySet", tagsPrimyKeySet);

Please let me know if you have any questions

Upvotes: -1

Related Questions