CODEWITHSUNDEEP
javahibernatespring-dataspring-data-jpa
Bit Fracture
Bit Fracture

Reputation: 717

CrudRepository findAll() By List Contains Sub-list

Preface

I am using Spring Data/JPA/Hibernate etc. and entities (annotated with javax.persistence.Entity) that are retrieved via repositories that extend org.springframework.stereotype.CrudRepository.

I have an entity, we'll call it Bob, and it contains a list of another type of Entity, Tom. This list is called List<Tom> toms;. This is a many-to-many relationship in my database.

@Repository
public interface BobRepository extends CrudRepository<Bob, String> {
    public Iterable<Bob> findAllByTomsContains(List<Tom> toms);
}

This is one of the automatically-implemented methods that Spring takes care of for me as long as I name it correctly.

My question

I need to fetch all Bobs where the provided list of Toms is a sub-set of the toms stored in each Bob. That is, the list I provide to the method is a sub-set of the list stored in the Bob entity.

Right now, with the above code, I am getting a Bob result for each item in the Toms list. So if one of my Bobs has these Toms {"tom-one", "tom-two", "tom-blue"} and I query for {"tom-one", "tom_two"} I am getting two matches to the same tom. Similarly, if I add an additional Tom that doesn't match bob's list, the first matching Tom still does, and is returned. A query like this should match Bobs with all of the listed Toms or more.

What is the appropriate naming convention for my method (currently findAllByTomsContains), if this is even possible? My current workaround is many queries by each Tom in the list, merging an intersection of results (slow, painful).

Upvotes: 1

Views: 3690

Answers (1)

Cepr0
Cepr0

Reputation: 30309

@Query("select b from Bob b join b.toms t where t in (?1) group by b having count(t) >= (select count(t2) from Tom t2 where t2 in (?1))")
Iterable<Bob> findIfSubsetOfTomsExists(List<Tom> toms);

Or:

@Query("select b from Bob b join b.toms t where t in (?1) group by b having count(t) >= ?2")
Iterable<Bob> findIfSubsetOfTomsExists(List<Tom> toms, int tomsCount);

default Iterable<Bob> getIfSubsetOfTomsExists(List<Tom> toms) {
    return findIfSubsetOfTomsExists(toms, toms.size());
}

Upvotes: 1

Related Questions