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

Question

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

Answer 1

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

Answer 2

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

Answer 3

[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.