Search code examples
sqlhqljpql

Can i use clause IN in JPQL for selecting both items?

I have softwares which related to tags - many-to-many. I want to create a query which would select all softwares which are related to all tags that i put as parameters.

I write:

public static List searchByTags(List tags) {

    TypedQuery query =
            Software.em().createQuery("SELECT DISTINCT s FROM Software s, IN(s.tags) t WHERE t IN(:tags)", Software.class);
    query.setParameter("tags", tags);

    return query.getResultList();
}

But it's not that i want. For example if i have:

soft1 -> tag1,tag2 
soft2 -> tag2

it would select me both. But i want only one - soft1. How to create such query?

Solution

  • i found the answer:

    TypedQuery query = Software.em().createQuery("SELECT s FROM Software s INNER JOIN s.tags tag WHERE tag IN (:tags) GROUP BY s HAVING COUNT(tag) = :size", Software.class);