I have an Entity "CategoryEntity" with a member like this:
@ElementCollection(targetClass = String.class, fetch = FetchType.EAGER)
@CollectionTable(name = "access_groups", joinColumns = @JoinColumn(name = "category_id"))
private Set<String> accessGroups = new LinkedHashSet<>();
Now I would like to query for categories that have at least one access_group in common with the provided access_group list provided as query param.
The query looks like this:
@Query(value = """
Select c from category c where
(c.accessGroups = null or c.accessGroups is empty)
OR c.accessGroups in (:accessGroupsInherited)
""")
Page<CategoryEntity> findAllBy(Pageable pageable, Collection<String> accessGroupsInherited);
accessGroupsInherited can contain zero to n elements.
CategoryEntity.accessGroups can contain zero to n elements.