获取父实体时筛选子实体并按更新日期获取首个子实体

Let's break down your two requirements and implement them step by step with your FinancialAidRequestEntity setup:

1. Filter Child Entities When Fetching Parent Entity #

You have a few flexible options to filter child entities during parent fetch:

Option 1: Global Filter with @Where Annotation #

If you want the filter to apply every time you fetch the parent entity, use Hibernate's @Where annotation directly on the @OneToMany association. This is great for static filters like excluding deleted records:

@Entity
@Table(name = "financial_aid_request_table")
public class FinancialAidRequestEntity extends BaseAuditingEntity {
    // ... existing fields

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "financialAidRequest")
    @Where(clause = "is_deleted = false") // Example: Only include non-deleted child entities
    private List<FinancialAidSubEntity> subEntities; // Replace with your actual child entity type

    // Getters and setters
}

Option 2: Dynamic Filter with Custom JPQL Query #

For ad-hoc filters (like filtering by date range or status), create a custom query in your repository. This lets you apply different filters based on the use case:

public interface FinancialAidRequestRepository extends JpaRepository<FinancialAidRequestEntity, Integer> {

    @Query("SELECT far FROM FinancialAidRequestEntity far " +
           "JOIN FETCH far.subEntities se " +
           "WHERE far.id = :requestId " +
           "AND se.updatedDate >= :startDate") // Dynamic filter condition
    FinancialAidRequestEntity findByIdWithFilteredChildren(
            @Param("requestId") Integer requestId,
            @Param("startDate") LocalDateTime startDate
    );
}

Option 3: Repository Method Naming Convention #

If your filter logic is simple, you can leverage Spring Data JPA's method naming magic to avoid writing JPQL:

public interface FinancialAidRequestRepository extends JpaRepository<FinancialAidRequestEntity, Integer> {
    // Filters child entities where status equals the given value
    FinancialAidRequestEntity findByIdAndSubEntities_Status(Integer id, String status);
}

2. Fetch Only the First Child Entity Sorted by Update Date #

To get just the latest (or earliest) child entity when fetching the parent, you have two main approaches:

Option 1: Custom JPQL Query with Subquery #

Use a subquery to fetch the top child entity sorted by updatedDate, then join it with the parent:

public interface FinancialAidRequestRepository extends JpaRepository<FinancialAidRequestEntity, Integer> {

    // Fetch parent with the latest child entity (sorted by updatedDate DESC)
    @Query("SELECT far FROM FinancialAidRequestEntity far " +
           "LEFT JOIN FETCH far.subEntities se " +
           "WHERE se.id = (" +
               "SELECT seTop.id FROM FinancialAidSubEntity seTop " +
               "WHERE seTop.financialAidRequest.id = far.id " +
               "ORDER BY seTop.updatedDate DESC " +
               "LIMIT 1" +
           ") " +
           "AND far.id = :requestId")
    FinancialAidRequestEntity findByIdWithLatestChild(@Param("requestId") Integer requestId);

    // To get the earliest child, change ORDER BY to ASC
    @Query("SELECT far FROM FinancialAidRequestEntity far " +
           "LEFT JOIN FETCH far.subEntities se " +
           "WHERE se.id = (" +
               "SELECT seTop.id FROM FinancialAidSubEntity seTop " +
               "WHERE seTop.financialAidRequest.id = far.id " +
               "ORDER BY seTop.updatedDate ASC " +
               "LIMIT 1" +
           ") " +
           "AND far.id = :requestId")
    FinancialAidRequestEntity findByIdWithEarliestChild(@Param("requestId") Integer requestId);
}

Option 2: Map to a Single Child Entity (If Appropriate) #

If your business logic only ever needs the latest child for a parent, you can change the @OneToMany association to a @OneToOne with a custom join condition. This makes fetching the top child automatic:

@Entity
@Table(name = "financial_aid_request_table")
public class FinancialAidRequestEntity extends BaseAuditingEntity {
    // ... existing fields

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "id", referencedColumnName = "financial_aid_request_id")
    @Where(clause = "(SELECT MAX(updated_date) FROM financial_aid_sub_table WHERE financial_aid_request_id = id) = updated_date")
    private FinancialAidSubEntity latestSubEntity;

    // Getters and setters
}

Note: This approach works best if you're using Hibernate and can rely on the @Where clause for the join condition.

内容的提问来源于stack exchange，提问作者parthivrshah