CODEWITHSUNDEEP
jpajpql
joshua
joshua

Reputation: 4198

Selecting where an entity contains a list thats a subset of another list

I am writing a JPQL query and i have the following scenario. I have a Question entity which contains a list of Tags. I would like to select all Questions that contains a given List of tags. How do i do this with JPA?

I would like to do something like SELECT x FROM Question x WHERE x.tags 'contains all' :tags

Upvotes: 12

Views: 16612

Answers (3)

Nayan Wadekar
Nayan Wadekar

Reputation: 11612

[This searches for ANY not ALL; please refer other correct answers.]

You can set list as a parameter. 

SELECT x FROM Question x WHERE x.tags IN :tags

Also try using (:tags), as it depends on the JPA implementation you are using.

Upvotes: 3

zyexal
zyexal

Reputation: 1579

Nayans solution does'nt work for me. Its selecting every 'x' which matches the first (or any?) entry of the given collection ':tags'. If this really worked for you, you should test you application again ;) might be JPA dependend - I don't know.

Tip: Try Krzysztofs solution or use mine: 

SELECT x FROM Question x 
WHERE x IN (
    SELECT y FROM Question y
    INNER JOIN y.tags yt
    WHERE yt IN (
        :tags
    )
    GROUP BY y
    HAVING COUNT( DISTINCT yt) = (
        :tagsSize // should be clear ;)
    )
)

Upvotes: 4

Krzysztof Kaczmarek
Krzysztof Kaczmarek

Reputation: 318

Try like this:

select distinct q from Question q join q.tags as t 
where t.name in (:tags) 
group by q.id, q.author, q.title, q.content,q.postedAt 
having count(t.id) = :size

Upvotes: 5

Related Questions