How to filter both entity and @OneToMany relationship by criteria api

Question

The following example filters "Entity2" according to the given criteria. However, the List of the @OneToMany relationship remains unfiltered (all entities are included, regardless of whether they match the filter). How to expand the filter so that these are also filtered? In this example entity2.entities1 may only include an Entity1 if the "value" is greater or equals the given one.

(postgres) database structure:

table1
-------------------
id character
id_table2 character
value integer

table2                                      
--------------                              
id character                                
name character                              
deletedat timestamp without time zone

entity model:

@Entity
@Table(name = "table1")
public class Entity1 {
  @Id
  private String id;

  @Column(name = "value")
  private Integer value;
  // ...
}

@Entity
@Table(name = "table2")
@Where(clause = "deletedat is null")
public class Entity2 {
  @Id
  private String id;

  @Column(name = "name")
  private String name;

  @OneToMany
  @JoinColumn(name = "id_table1", updatable = false)
  private List<Entity1> entities1;
  // ...
}

call and specification:

public Page<Entity2> findByFilter(SomeFilter filter) {
  Pageable pageable = //...
  return entity2Repository.findAll(createSpecification(filter), pageable);
}

private Specification<Entity2> createSpecification(SomeFilter filter) {
  return (root, query, criteriaBuilder) -> {
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(criteriaBuilder.equal(root.get("name"), filter.getName()));
    root.join("entities1").on(criteriaBuilder.greaterThanOrEqualTo(join.get("value"), filter.getMinValue()));
    query.distinct(true);
    return criteriaBuilder.and(predicates.stream().toArray(Predicate[]::new));
  };
}

I already tired solutions from this questions but without success:

• JPA Criteria Join OneToMany table where clause does not work
• JPA Criteria Builder OneToMany Restrictions
• https://stackoverflow.com/a/48441456/3615209

Answer 1

First of all, it's better to define the relationship in both classes.

Therefore, from Entity1 to Entity2 there is a @ManyToOne relationship and from Entity2 to Entity1 there is a @OneToMany relationship.

@Entity
@Table(name = "table1")
public class Entity1 {
  @Id
  private String id;

  @Column(name = "value")
  private Integer value;

  @ManyToOne
  @JoinColumn(name = "id_table2", referencedColumnName = "id", updatable = false)
  private Entity2 entity2;

  // ...
}

@Entity
@Table(name = "table2")
@Where(clause = "deletedat is null")
public class Entity2 {
  @Id
  private String id;

  @Column(name = "name")
  private String name;

  @OneToMany(mappedBy = "entity2")
  private List<Entity1> entities1;
  // ...
}

In the end, you have to return a list of entity1, therefore you can reverse the selection and you can start from entity1. In this way, you can use fetch to avoid extra queries and you can navigate with get.

private Specification<Entity1> createSpecification(SomeFilter filter) {
  return (root, query, criteriaBuilder) -> {
    List<Predicate> predicates = new ArrayList<>();
    root.fetch("entity2");
    predicates.add(criteriaBuilder.equal(root.get("entity2").get("name"), 
                                     filter.getName()));
    predicates.add(criteriaBuilder.greaterThanOrEqualTo(
                                             root.get("value"), 
                                             filter.getMinValue()));
    query.distinct(true);
    return criteriaBuilder.and(predicates.stream().toArray(Predicate[]::new));
  };
}