JPA JPQL select where one element in param list matches entity list

27 Views Asked by At

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.

0

There are 0 best solutions below