'How to get only those objects whose subarrays in the arrays match the requested one with SQL and Hibernate?

I have Project objects that contain arrays of Tags (for example, Backend, Frontend, Sales, Marketing).

The user sets the desired parameters, such as Backend and Frontend. It's array of Tags.

And I need to find all the Projects, where Tags included Backend and also Frontend.

I can write something like this using the IN operator (SQL), but it will select elements that will contain either the Backend or the Frontend, and not these two Tags together.

For example, my JPA Repository method looks like it:

List<Project> findProjectsByNameContainingAndTagListNameInOrderById(String name, List<String> tagNameList, Pageable pageable);

Then I get a list like this, where there are elements in which the arrays contain at least 1 of the elements of the requested subarray. To remove all the unnecessary, I use this method:

    // Delete All Projects Which Don't Have All Tags From TagNameList

    private void deleteSameTags(@RequestParam(required = false) List<String> tagNameList, Set<Project> projects) {

        //...

        projects.removeIf(project -> !project.getTagList().containsAll(tagNameList));

    }

It helps me right now, but I think that this can be implemented in a simpler and more convenient way using SQL or Hibernate.

javasqlspringpostgresqlhibernate


Solution 1:[1]

You can use something like this

select p from Project p join p.tagList t
where t in :tags group by p.id having count(p.id) = :tagCount

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 teczarina

Related Questions