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

jpa jpql
joshua picture joshua · Jan 9, 2011 · Viewed 14k times · Source

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

Krzysztof Kaczmarek picture Krzysztof Kaczmarek · Feb 26, 2012

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

Related questions

Adding IN clause List to a JPA Query

I have built a NamedQuery that looks like this: @NamedQuery(name = "EventLog.viewDatesInclude", query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND " + "el.timeMark <= :dateTo AND " + "el.name IN (:inclList)") What I want to do is …

Read More
JPQL IN clause: Java-Arrays (or Lists, Sets...)?

I would like to load all objects that have a textual tag set to any of a small but arbitrary number of values from our database. The logical way to go about this in SQL would be to build an "…

Read More
IN-clause in HQL or Java Persistence Query Language

I have the following parametrised JPA, or Hibernate, query: SELECT entity FROM Entity entity WHERE name IN (?) I want to pass the parameter as an ArrayList<String>, is this possible? Hibernate current tells me, that java.lang.ClassCastException: …

Read More