could anybody help?

Entity A has a field with set of enums.

Method takes a set of enums.

Task - filter out values if A.field has 1 or more elements in common with passed set of enums. I was trying to achieve it via INTERSECT, but the result wasn't achieved.

Working solution - NOT MEMBER OF, but it could be used only for 1 parameter... possible to implement with for(), but I am sure that it isn't a good practice...

CodePudding user response：

There is no direct way to compare one collection to another in JPA.

Solutions around the issue though involve subqueries, very similar what JPA will do to evaluate your MEMBER OF clause. A sub-query lets you look at individual values in a collection and compare it to the other, and use that result in your main query. Something along the lines of:

"Select a from A a where a.id not in (select distinct aPrime.id from A aPrime join aPrime.field field where field in :parameterList)"

I'm not a DBA, but if one complains this is inefficient, there many other ways of being expressed, possibly with exists:

"Select a from A a where exists (select aPrime from A aPrime join aPrime.field field where field in :parameterList and aPrime.id = a.id)"