如何用JPA CriteriaBuilder按指定用户评分排序发票列表

需要基于三个关联实体（Invoice、InvoiceRating、User），用JPA CriteriaBuilder实现以下要求：

• 生成按指定用户的评分排序的发票列表
• 无对应评分的发票必须纳入列表
• 在现有分页排序代码基础上，添加按指定用户过滤InvoiceRating的逻辑

实体代码 #

@Entity
public class Invoice  {
 @OneToMany(mappedBy = "invoice", fetch = FetchType.LAZY)
  Set<InvoiceRating> ratings;
}
@Entity
public class InvoiceRating {
    @EmbeddedId
    BusinessTransactionRating ratingKey;

    @ManyToOne
    @MapsId("userId")
    @JoinColumn(name = "user_id")
    User user;

    @ManyToOne
    @MapsId("transactionId")
    @JoinColumn(name = "invoice_id")
    Invoice invoice;

    int rating;
}
@Embeddable
public class BusinessTransactionRating {
    @Column(name = "transaction_id")
    Long transactionId;

    @Column(name = "user_id")
    Long userId;

}
@Entity
public class User {
}

现有可运行代码 #

CriteriaQuery<Invoice> criteriaQuery = cBuilder.createQuery(Invoice.class);
Root<Invoice> rootInvoice = criteriaQuery.from(Invoice.class);

pageable.getSort().stream().forEach(order -> { // sorting
         Path<Object> prop = rootInvoice.get(order.getProperty());
     list.add(order.isAscending() ? cBuilder.asc(prop) : cBuilder.desc(prop));
            // finalQuery.orderBy();
        });
CriteriaQuery<Invoice> criteriaQueryOrder = finalQuery.orderBy(list);
        // TO DO / Add the count Line and the rebuild pageImpl
TypedQuery<Invoice> queryPaginate = entityManager.createQuery(criteriaQueryOrder);

解决方案 #

要实现需求，核心是用左连接关联Invoice和InvoiceRating，同时过滤指定用户的评分，再通过CASE WHEN处理无评分的发票排序逻辑，最后整合分页计数。

1. 添加左连接与用户过滤 #

先对Invoice的ratings集合做左连接，保证无评分的Invoice不会被过滤，同时加入指定用户的过滤条件：

// 左连接Invoice和InvoiceRating，保留所有Invoice
Join<Invoice, InvoiceRating> ratingJoin = rootInvoice.join("ratings", JoinType.LEFT);

// 替换为实际要指定的用户ID
Long targetUserId = 100L;
// 过滤该用户的评分，同时允许无评分的情况（左连接后rating为null）
Predicate ratingFilter = cBuilder.or(
    cBuilder.equal(ratingJoin.get("user").get("id"), targetUserId),
    cBuilder.isNull(ratingJoin.get("rating"))
);
criteriaQuery.where(ratingFilter);

2. 修改排序逻辑，兼容无评分场景 #

原来的排序只处理Invoice自身属性，现在要加入按指定用户评分排序的逻辑，用CASE WHEN给无评分的发票设默认值（比如0，可按需调整）：

List<Order> orders = new ArrayList<>();

pageable.getSort().stream().forEach(order -> {
    // 假设分页排序参数中用"userRating"表示按指定用户评分排序
    if ("userRating".equals(order.getProperty())) {
        Expression<Integer> ratingExpr = cBuilder.selectCase()
                .when(cBuilder.isNotNull(ratingJoin.get("rating")), ratingJoin.get("rating"))
                .otherwise(0); // 无评分的发票默认按0排序
        orders.add(order.isAscending() ? cBuilder.asc(ratingExpr) : cBuilder.desc(ratingExpr));
    } else {
        // 原有Invoice属性的排序逻辑
        Path<Object> prop = rootInvoice.get(order.getProperty());
        orders.add(order.isAscending() ? cBuilder.asc(prop) : cBuilder.desc(prop));
    }
});

criteriaQuery.orderBy(orders);

3. 完善分页计数逻辑 #

分页的总数查询必须和主查询用相同的连接与过滤条件，否则会导致总数不准确：

// 构建计数查询
CriteriaQuery<Long> countQuery = cBuilder.createQuery(Long.class);
Root<Invoice> countRoot = countQuery.from(Invoice.class);
Join<Invoice, InvoiceRating> countRatingJoin = countRoot.join("ratings", JoinType.LEFT);
Predicate countFilter = cBuilder.or(
    cBuilder.equal(countRatingJoin.get("user").get("id"), targetUserId),
    cBuilder.isNull(countRatingJoin.get("rating"))
);
// 用distinct避免左连接导致的重复计数
countQuery.select(cBuilder.countDistinct(countRoot)).where(countFilter);
Long total = entityManager.createQuery(countQuery).getSingleResult();

// 设置分页参数并执行查询
queryPaginate.setFirstResult((int) pageable.getOffset());
queryPaginate.setMaxResults(pageable.getPageSize());
List<Invoice> invoiceList = queryPaginate.getResultList();

// 构建Page返回结果
Page<Invoice> resultPage = new PageImpl<>(invoiceList, pageable, total);

完整整合代码 #

// 初始化查询
CriteriaQuery<Invoice> criteriaQuery = cBuilder.createQuery(Invoice.class);
Root<Invoice> rootInvoice = criteriaQuery.from(Invoice.class);
Long targetUserId = 100L; // 替换为实际目标用户ID

// 左连接并过滤指定用户评分
Join<Invoice, InvoiceRating> ratingJoin = rootInvoice.join("ratings", JoinType.LEFT);
Predicate ratingFilter = cBuilder.or(
    cBuilder.equal(ratingJoin.get("user").get("id"), targetUserId),
    cBuilder.isNull(ratingJoin.get("rating"))
);
criteriaQuery.where(ratingFilter);

// 处理排序
List<Order> orders = new ArrayList<>();
pageable.getSort().stream().forEach(order -> {
    if ("userRating".equals(order.getProperty())) {
        Expression<Integer> ratingExpr = cBuilder.selectCase()
                .when(cBuilder.isNotNull(ratingJoin.get("rating")), ratingJoin.get("rating"))
                .otherwise(0);
        orders.add(order.isAscending() ? cBuilder.asc(ratingExpr) : cBuilder.desc(ratingExpr));
    } else {
        Path<Object> prop = rootInvoice.get(order.getProperty());
        orders.add(order.isAscending() ? cBuilder.asc(prop) : cBuilder.desc(prop));
    }
});
criteriaQuery.orderBy(orders);

// 分页查询
TypedQuery<Invoice> queryPaginate = entityManager.createQuery(criteriaQuery);
queryPaginate.setFirstResult((int) pageable.getOffset());
queryPaginate.setMaxResults(pageable.getPageSize());
List<Invoice> invoiceList = queryPaginate.getResultList();

// 计数查询
CriteriaQuery<Long> countQuery = cBuilder.createQuery(Long.class);
Root<Invoice> countRoot = countQuery.from(Invoice.class);
Join<Invoice, InvoiceRating> countRatingJoin = countRoot.join("ratings", JoinType.LEFT);
Predicate countFilter = cBuilder.or(
    cBuilder.equal(countRatingJoin.get("user").get("id"), targetUserId),
    cBuilder.isNull(countRatingJoin.get("rating"))
);
countQuery.select(cBuilder.countDistinct(countRoot)).where(countFilter);
Long total = entityManager.createQuery(countQuery).getSingleResult();

// 构建分页结果
Page<Invoice> resultPage = new PageImpl<>(invoiceList, pageable, total);

关键注意点 #

• 必须用LEFT JOIN，否则无评分的Invoice会被排除
• 过滤条件要包含cBuilder.isNull(ratingJoin.get("rating"))，避免左连接后过滤掉无评分数据
• 计数查询用countDistinct，防止左连接导致同一Invoice被多次计数
• 无评分的默认排序值可根据业务调整，比如设为最低分或最高分

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