Hibernate query to filter results from a nested object list

Question

As a followup to this answer(on approach 1 ) I want to go a step further :

I want to Filter the grand child objects based on certain criteria's. I tried the following query, but it still does not filter out the objects under the grandchild entity.

  @Query("select ch from ChildEntity ch "
      + " join ch.parentEntity pr "
      + " join fetch ch.grandChildEntities gc "
      + " where pr.bumId = :bumId and ch.lastExecutionTimestamp in "
      + "( select max(ch1.lastExecutionTimestamp) from ChildEntity ch1 "
      + "join ch1.grandChildEntities gc ON ch1.id = gc.childEntity where "
      + "gc.field1 in ('\"Criteria1\"','\"Criteria2\"','\"Criteria3\"') and " 
      + "gc.field2 = '\"soldout\"'"
      + "ch1.parentEntity = pr group by ch1.c1))")
 List<ChildEntity> findLastExecutedChildFromBumId(@Param("bumId") String bumId);

Associated Class Entities

Class Relation ParentEntity <1-oneToMany-x> ChildEntity<1-oneToMany-x>GrandChildEntity

@Entity
@Getter
@Setter
@Table(name = "table_parent")
@RequiredArgsConstructor
@NoArgsConstructor
@AllArgsConstructor
public class ParentEntity implements Serializable {
    
   private static final long serialVersionUID = -271246L;
    
   @Id
   @SequenceGenerator(
      name="p_id",
      sequenceName = "p_sequence",
      initialValue = 1,
      allocationSize = 1)
   @GeneratedValue(generator="p_id")
   @Column(name="id", updatable=false, nullable=false)
   private Long id;
    
   @NonNull
   @Column(name ="bum_id", nullable = false, unique = true)
   private String bumId;
    
   @NonNull
   @Column(nullable = false, length = 31)
   private String f1;
    
   @NonNull
   @Column(nullable = false, length = 31)
   private String f2;
    
   @NonNull
   @Column( nullable = false, length = 255)
   @Convert(converter = JpaConverterJson.class)
   private List<String> f3;
    
   @NonNull
   @Column(nullable = false)
   private String f4;
    
   @NonNull
   @Column(name = "es_status", nullable = false, length = 255)
   @Enumerated(EnumType.STRING)
   private ExecutionStatus esStatus;
    
   @JsonManagedReference
   @OneToMany(mappedBy = "parentEntity", cascade = CascadeType.ALL,
       fetch = FetchType.EAGER)
   @Setter(AccessLevel.NONE)
   private List<ChildEntity> childEntities;
    
   public void setChildEntities(List<ChildEntity> childEntities) {
      this.childEntities = childEntities;
      childEntities.forEach(entity -> entity.setParentEntity(this));
   }
}


@Entity
@Getter
@Setter
@Table(name= "table_child")
@NoArgsConstructor
public class ChildEntity implements Serializable {
   private static final long serialVersionUID =  -925587271547L;
    
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
    
   @JsonBackReference
   @ManyToOne(fetch = FetchType.EAGER )
   @JoinColumn(name = "parent_id")
   private ParentEntity parentEntity;
    
   @Column(name = "c1",nullable = false)
   @NonNull
   @Convert(converter = JpaConverterJson.class)
   private String c1;
    
   @Column(name = "last_exec_status",nullable = false)
   @NonNull
   @Enumerated(EnumType.STRING)
   private ExecutionStatus lastExecStatus;
    
   @Column(name = "c4",nullable = false)
   @NonNull
   private String  c4;
    
   @Column(name = "last_execution_timestamp",nullable = false)
   @NonNull
   private long lastExecutionTimestamp;
    
   @JsonManagedReference
   @NonNull
   @OneToMany(mappedBy = "childEntity", cascade = CascadeType.ALL,
      fetch = FetchType.EAGER)
   @Setter(AccessLevel.NONE)
   private List<GrandChildEntity> grandChildEntities;
    
   public void setGrandChildEntities(List<GrandChildEntity> grandChildEntities) {
      this.grandChildEntities = grandChildEntities;
      grandChildEntities.forEach(entity -> entity.setChildEntity(this));
   }
}


@Entity
@Getter
@Setter
@Table(name="table_grand_child")
@NoArgsConstructor
//@AllArgsConstructor
public class GrandChildEntity implements Serializable {
   private static final long serialVersionUID = -925567241248L;
    
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
    
   @JsonBackReference
   @ManyToOne(fetch = FetchType.EAGER )
   @JoinColumn(name = "child_entity_id")
   private ChildEntity childEntity;
    
   @Column(name="gc1",nullable = false)
   private String gc1;
    
   @Column(name="gc2",nullable = false)
   private String gc2;
    
   @Column(name="gc3",nullable = false)
   private String gc3;
    
   @Column(name="gc3",nullable = true)
   private List<String> gc3;
}

Answer 1

Filtering a collection that is join fetched is a bad idea as that alters the "persistent state" and might cause entities to be removed due to that. I suggest you use a DTO approach instead.

I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(ChildEntity.class)
public interface ChildEntityDto {
    @IdMapping
    Long getId();
    String getC1();
    ParentEntityDto getParentEntity();
    @Mapping("grandChildEntities[field1 in ('\"Criteria1\"','\"Criteria2\"','\"Criteria3\"') and gc.field2 = '\"soldout\"']")
    Set<GrandChildEntityDto> getGrandChildEntities();

    @EntityView(ParentEntity.class)
    interface ParentEntityDto {
        @IdMapping
        Long getId();
        String getF1();
    }
    @EntityView(GrandChildEntity.class)
    interface GrandChildEntityDto {
        @IdMapping
        Long getId();
        String getGc1();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

UserDto a = entityViewManager.find(entityManager, UserDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features