How to check if entity in set contains a certain value using criteriaBuilder?

Question

I have the following classes:

@Entity
public class Object {
   @ManyToOne
   @JoinColumn(name="RelationId", referencedColumnName = "ID", nullable=true)
   private Relation relation;
}

@Entity
public class Relation{
   @OneToMany(mappedBy="relation")
   private Set<Object> objects = new HashSet<>();

   @OneToMany(mappedBy="relation")
   private Set<Contact> contactpersons = new HashSet<>();
}

@Entity
public class Contact{
   @ManyToOne
   @JoinColumn(name="RelationId")
   private Relation relation;

   private Boolean isPrimary;
}

and this is some code of my rootQuery:

private Stream<Specification<Object>> toSpec(List<SearchCriteria> searchCriteria){
   return searchCriteria.stream().map(criteria ->{
        if (criteria.isSet(ObjectKeys.searchObject)) {
            return (Specification<Object>) (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
           
            [Here a criteriabuilder predicate needs to go that check if object has relation that has 
            contacts that have isPrimary on true]

           return criteriaBuilder.or(predicates.toArray(new Predicate[0]));
        }
    }
   }    
}

How can I check if the relation of an object contains a contact person with isPrimary on true using criteriabuilder

I tried using criteriaBuilder.in(), but that can only check if a contact object is in the set and not if a contact object has a certain value. I also tried doing it with joins but that does not give me the correct result, because I can only use Left, inner or right joins which result in duplicate objects or non-nullable objects.

Answer 1

What you want here, is a so called semi-join in relational algebra which is modeled through the EXISTS predicate in SQL and also supported in JPA. You have to use a correlated subquery with an exists predicate. Something along the lines of the following:

private Stream<Specification<Object>> toSpec(List<SearchCriteria> searchCriteria){
   return searchCriteria.stream().map(criteria ->{
        if (criteria.isSet(ObjectKeys.searchObject)) {
            return (Specification<Object>) (root, query, criteriaBuilder) -> {
                Subquery<Integer> subquery = query.subquery(Integer.class);
                Root<Object> correlated = subquery.correlate(root);
                Join<?, ?> join = correlated.join("relation").join("contactpersons");
                subquery.select(criteriaBuilder.literal(1));
                subquery.where(criteriaBuilder.isTrue(join.get("isPrimary")));
                return criteriaBuilder.exists(subquery);
            }
        }
   }    
}