Blaze Persistence分页查询排序含标识符仍报唯一元组错误

问题描述 #

开发Spring Boot应用时，使用Blaze Persistence构建包含多关联表的Criteria查询以获取分页结果，遭遇错误提示：

The order by items of the query builder are not guaranteed to produce unique tuples! Consider also ordering by the entity identifier!

已尝试在排序子句中添加o.orderId，但错误仍未解决；即使移除自定义排序逻辑，问题依旧存在。期望查询能无错误执行并返回可用于分页的唯一元组结果。

实体类与查询代码 #

实体类定义 #

@Getter
@Setter
@Entity
@Table(name = "orders")
public class OrderEntity {
    @Id
    private Long orderId;
    private String orderCode;

    @OneToMany(mappedBy = "orderEntity", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderAttribute> attributes = new ArrayList<>();
    
    @OneToMany(mappedBy = "orderEntity", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderAction> actions = new ArrayList<>();
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "issue_type_id", nullable = false)
    private IssueType issueType;
}

@Getter
@Setter
@Entity
public class OrderAction {
    @Id
    private Long actionId;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private OrderEntity orderEntity;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "current_location_id", nullable = false)
    private Location currentLocation;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "next_location_id", nullable = false)
    private Location nextLocation;
}

@Getter
@Setter
@Entity
public class OrderAttribute {
    @Id
    private Long attributeId;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private OrderEntity orderEntity;
    
    private String key;
    private String value;
}

@Getter
@Setter
@Entity
public class IssueType {
    @Id
    private Long typeId;
    
    @Column(nullable = false, unique = true)
    private String type;
}

@Getter
@Setter
@Entity
public class Location {
    @Id
    private Integer locationId;
    private String locationCode;
    private String name;
    private Integer parentLocationId;
}

查询实现代码 #

@Component
public class OrderRepositoryCustomImpl implements OrderRepositoryCustom {

    @PersistenceContext
    private EntityManager em;
    private final CriteriaBuilderFactory cbf;

    public OrderRepositoryCustomImpl(EntityManager em, CriteriaBuilderFactory cbf) {
        this.em = em;
        this.cbf = cbf;
    }

    @Override
    public Page<OrderListProjection> search(SearchRequest searchRequest, Pageable pageable) {
        CriteriaBuilder<Tuple> cb = cbf.create(em, Tuple.class)
                .from(OrderEntity.class, "o")
                .select("o.orderId", "orderId")
                .select("o.orderCode", "orderCode")
                .select("loc.locationId", "nextLocationId")
                .select("loc.name", "nextLocationName")
                .select("it.type", "issueType")
                .select("oa.value", "attributeValue")
                .select("loc.parentLocationId", "parentLocationId")
                .select("loc.name", "parentLocationName")
                .select("loc.locationCode", "locationCode")
                .innerJoin("o.issueType", "it")
                .leftJoinLateralOnEntitySubquery("o.actions", "topAction", "action")
                .orderByDesc("action.actionId")
                .setMaxResults(1)
                .end()
                .on("1").eqExpression("1")
                .end()
                .leftJoin("topAction.nextLocation", "loc")
                .leftJoinOnEntitySubquery("o", OrderAttribute.class, "oa")
                .end()
                .on("oa.orderEntity").eqExpression("o")
                .on("oa.key").eq("attributeKey").end();

        if (searchRequest.getIssueType() != null) {
            cb.where("it.type").eq(searchRequest.getIssueType());
        }
        if (searchRequest.getParentLocationId() != null) {
            cb.where("loc.parentLocationId").eq(searchRequest.getParentLocationId());
        }
        if (searchRequest.getAttributeValue() != null) {
            cb.where("oa.value").eq(searchRequest.getAttributeValue());
        }

        PaginatedCriteriaBuilder<Tuple> builder = cb.page((int) pageable.getOffset(), pageable.getPageSize());

        pageable.getSort().forEach(order -> {
            String property = order.getProperty();
            if (order.isAscending()) {
                builder.orderByAsc("o." + property);
            } else {
                builder.orderByDesc("o." + property);
            }
        });

        builder.orderByAsc("o.orderId");

        PagedList<Tuple> resultList = builder.withKeysetExtraction(true).getResultList();

        List<OrderListProjection> list = resultList.stream()
                .map(tuple -> new OrderListProjection(
                        tuple.get("orderId", Long.class),
                        tuple.get("orderCode", String.class),
                        tuple.get("nextLocationId", Integer.class),
                        tuple.get("nextLocationName", String.class),
                        tuple.get("issueType", String.class),
                        tuple.get("attributeValue", String.class),
                        tuple.get("parentLocationId", Integer.class),
                        tuple.get("parentLocationName", String.class),
                        tuple.get("locationCode", String.class)))
                .toList();

        return new PageImpl<>(list, pageable, resultList.getTotalSize());
    }
}

解决方案 #

核心原因 #

查询中通过leftJoinLateralOnEntitySubquery关联OrderAction、leftJoinOnEntitySubquery关联OrderAttribute，导致同一个OrderEntity可能对应多条结果行（比如一个订单有多个属性记录）。此时仅按OrderEntity的orderId排序无法保证结果集中每一行的元组唯一——同一订单的不同属性行orderId相同，排序后行顺序不固定，不符合Blaze Persistence（尤其是Keyset分页）对排序键唯一性的要求。

具体修复方案 #

1. 补充排序字段，确保元组唯一

将关联表的主键加入排序，保证每一行的排序键组合唯一：

pageable.getSort().forEach(order -> {
    String property = order.getProperty();
    if (order.isAscending()) {
        builder.orderByAsc("o." + property);
    } else {
        builder.orderByDesc("o." + property);
    }
});

// 追加关联表主键，确保排序后元组唯一
builder.orderByAsc("o.orderId")
       .orderByAsc("oa.attributeId")  // 覆盖OrderAttribute导致的重复行
       .orderByAsc("topAction.actionId");  // 覆盖OrderAction子查询的潜在重复

2. 优化查询逻辑，避免结果行重复

如果业务需求是每个订单仅返回一条结果，调整关联逻辑确保不会产生重复行：

• 对OrderAttribute的关联子查询添加setMaxResults(1)，限制每个订单仅返回一条属性记录：

.leftJoinOnEntitySubquery("o", OrderAttribute.class, "oa")
.on("oa.orderEntity").eqExpression("o")
.on("oa.key").eq("attributeKey") // 这里的"attributeKey"需匹配业务指定的键
.setMaxResults(1) // 确保每个订单仅取一条属性
.end()

• 确认leftJoinLateralOnEntitySubquery已通过orderByDesc("action.actionId").setMaxResults(1)保证每个订单仅取一条最新的OrderAction，这部分逻辑是正确的。

3. 临时关闭Keyset分页（不推荐）

若不需要Keyset分页的性能优势，可移除.withKeysetExtraction(true)改用普通offset分页：

PagedList<Tuple> resultList = builder.getResultList();

该方案仅适合小数据量场景，大数据量下分页性能会显著下降。

验证要点 #

• 执行查询后检查结果集，确认是否存在orderId相同但其他字段不同的行
• 确保排序字段组合能唯一标识每一行，Blaze Persistence会验证排序键的唯一性以保证分页正确性

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