CODEWITHSUNDEEP
jpaspring-data-jpa
isADon
isADon

Reputation: 3673

JPA List IN List

For a given list of tags I want to find all users that have all those tags.

@ElementCollection
private Set<UserTag> tags;

Following is my JPA query

@Query("select u from users u where u.userData.tags IN :tags")
Page<User> findAllByUserDataTags(Pageable page, Set<UserTag> tags);

This query fails with following exception

java.lang.IllegalArgumentException: Parameter value [FEMALE] did not match expected type [java.util.Set (n/a)]
    at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:54) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:27) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.hibernate.query.internal.QueryParameterBindingImpl.validate(QueryParameterBindingImpl.java:90) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:55) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.hibernate.query.internal.QueryParameterBindingsImpl.expandListValuedParameters(QueryParameterBindingsImpl.java:640) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1564) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.hibernate.query.Query.getResultList(Query.java:165) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:177) ~[spring-data-jpa-2.3.3.RELEASE.jar

How can I find all users matching a given list of tags?

Upvotes: 1

Views: 160

Answers (1)

crizzis
crizzis

Reputation: 10716

Ummm... something like this?

SELECT u
FROM Users u
JOIN u.userData userData
JOIN userData.tags tag
WHERE tag IN :tags
GROUP BY u.id
HAVING COUNT(tag) = :tagsSize

Your original solution doesn't work because the IN operator translates to a SQL IN clause, and that clause generally only works for scalars on the LHS.

Upvotes: 1

Related Questions