如何使JPA查询返回仅包含指定用户投诉的Review实体？

你写的LEFT JOIN仅在SQL层面关联了符合条件的Complaint，但JPA默认会从持久化上下文加载Review关联的全部Complaint集合，并不会把过滤后的结果映射到Review的complaintJpaEntities属性中。

方法1：LEFT FETCH JOIN + DISTINCT（直接映射到实体，推荐） #

修改查询语句，用LEFT FETCH JOIN加载过滤后的Complaint集合，同时添加DISTINCT避免一对多关联导致的重复Review实体：

@Query("SELECT DISTINCT r "
        + "FROM ReviewJpaEntity r "
        + "LEFT JOIN FETCH r.complaintJpaEntities c "
        + "ON c.userJpaEntity.id = :userId")
List<ReviewJpaEntity> findAllWithComplaintByComplaintUserId(Long userId);

注意事项 #

• 若仍出现重复实体，可添加查询提示关闭Hibernate的DISTINCT传递：@QueryHints(value = @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_PASS_DISTINCT_THROUGH, value = "false"))
• 该方式会直接修改返回的Review实体关联集合，适合需要直接操作实体的场景。

方法2：构造DTO投影（灵活避免实体污染） #

如果不想修改原实体的关联集合，可定义DTO类直接查询组装需要的结构：

1. 定义DTO类 #

public class ReviewWithUserComplaintsDTO {
    private String reviewId;
    private List<ComplaintDTO> complaints;

    // 构造函数需与查询字段顺序对应
    public ReviewWithUserComplaintsDTO(String reviewId, List<ComplaintDTO> complaints) {
        this.reviewId = reviewId;
        this.complaints = complaints;
    }

    // Getter/Setter

    public static class ComplaintDTO {
        private String id;
        private String userId;

        public ComplaintDTO(String id, String userId) {
            this.id = id;
            this.userId = userId;
        }

        // Getter/Setter
    }
}

2. 编写查询语句 #

@Query("SELECT new com.yourpackage.ReviewWithUserComplaintsDTO("
        + "r.reviewId, "
        + "CASE WHEN c.id IS NOT NULL THEN "
        + "  COLLECT(new com.yourpackage.ReviewWithUserComplaintsDTO.ComplaintDTO(c.id, c.userJpaEntity.id)) "
        + "ELSE "
        + "  EMPTY_LIST "
        + "END) "
        + "FROM ReviewJpaEntity r "
        + "LEFT JOIN r.complaintJpaEntities c ON c.userJpaEntity.id = :userId "
        + "GROUP BY r.reviewId")
List<ReviewWithUserComplaintsDTO> findAllWithComplaintByComplaintUserId(Long userId);

该方式直接返回目标结构，不会影响原实体的持久化状态，适合接口返回场景。

方法3：JPA @Filter注解（全局固定条件过滤） #

若需全局范围内对Review的Complaint集合按用户过滤，可在实体上添加注解：

1. 在Review实体定义Filter #

@Entity
@FilterDef(name = "filterComplaintsByUserId", parameters = @ParamDef(name = "userId", type = Long.class))
@Filter(name = "filterComplaintsByUserId", condition = "user_jpa_entity_id = :userId")
public class ReviewJpaEntity {
    // 其他字段
    @OneToMany(mappedBy = "review")
    private List<ComplaintJpaEntity> complaintJpaEntities;
}

2. 查询时启用Filter #

@Autowired
private EntityManager entityManager;

public List<ReviewJpaEntity> findAllWithComplaintByComplaintUserId(Long userId) {
    entityManager.unwrap(org.hibernate.Session.class)
                .enableFilter("filterComplaintsByUserId")
                .setParameter("userId", userId);
    List<ReviewJpaEntity> reviews = reviewRepository.findAll();
    entityManager.unwrap(org.hibernate.Session.class)
                .disableFilter("filterComplaintsByUserId");
    return reviews;
}

该方式适合多场景复用过滤条件的场景，但需注意在Session/EntityManager层面手动启用和关闭Filter。

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