我使用JPA和Hibernate来访问PostgreSQL中的数据。
我需要通过positive=true的ReviewLikes计数对Reviews列表进行排序。但是当我尝试通过排序和分页获取Reviews列表（包括所有关系）时，我看到了与Criteria API和原生SQL查询不同的结果。如果我使用原生SQL -排序工作正常。如果我使用Criteria API返回错误的结果。排序完成，但不完全，一些元素是无序的。
我也尝试过使用Blaze持久性，但没有成功。
我怀疑错误是在我的标准的某个地方，但我一直在尝试为第4天找到至少一些类似的情况下，没有成功。
项目有4个实体：
1.网络资源

@Entity
@Table(name = "web_resource")
public class WebResource {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    private String url;

    @Enumerated(EnumType.STRING)
    @Column(name = "resource_type")
    private ResourceType resourceType;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="parent_id")
    private WebResource parent;
}

字符串
1.评论

@Entity
@Table(name = "reviews")
public class Review {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    @Column(name = "user_id")
    private String userId;

    @Column(name = "resource_id")
    private String resourceId;

    @Column(name = "review_id")
    private String reviewId;

    private Byte rating;

    private String text;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "resource_id", nullable = false, insertable = false, updatable = false)
    private WebResource webResource;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false, insertable = false, updatable = false)
    private User user;

    @OneToMany(mappedBy = "review", fetch = FetchType.LAZY)
    private Set<ReviewLike> likes;
}

型
1.用户

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    @Column(name = "user_name")
    private String userName;

    private String email;

    private String password;

    @Column(name = "full_name")
    private String fullName;

    @Column(name = "avatar_file_name")
    private String avatarFileName;

    @Column(name = "email_confirmed")
    private Boolean emailConfirmed;
}

型
1.评论点赞

@Entity
@Table(name = "reviews_likes")
public class ReviewLike {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    @Column(name = "user_id")
    private String userId;

    @Column(name = "review_id")
    private String reviewId;

    private Boolean positive;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "review_id", nullable = false, insertable = false, updatable = false)
    private Review review;
}

型
我尝试了原生SQL，它工作得很好（排序正确）：

select review.*
from reviews review
         join web_resource webResource on review.resource_id = webResource.id
         left join reviews_likes reaction on review.id = reaction.review_id
         join users u on review.user_id = u.id
group by review.id, webResource.url, webResource.resource_type
order by sum(case when reaction.positive = true then 1 else 0 end) DESC
limit 10 offset 0;

型
但是JPA Criteria API（Spring规范）返回的列表顺序错误：

List<Predicate> predicates = new ArrayList<>();

SetJoin<Review, ReviewLike> reviewLikeJoin = reviewRoot.joinSet(Review_.LIKES);
Expression<?> sortExpression sortExpression = criteriaBuilder.sum(
    criteriaBuilder.<Integer>selectCase()
    .when(criteriaBuilder.equal(reviewLikeJoin.get(ReviewLike_.POSITIVE), true), 1)
    .otherwise(0)
);

criteriaQuery.groupBy(reviewRoot.get(Review_.ID));
criteriaBuilder.desc(sortExpression);

型
Blaze持久性也不能正常工作：

CriteriaBuilder<Review> criteriaBuilder = criteriaBuilderFactory.create(entityManager, Review.class, "review")
            .leftJoinFetch("review.likes", "reviewLike")
            .leftJoinFetch("review.webResource", "webResource")
            .leftJoinFetch("review.user", "user")
            .groupBy("review.id", "review.likes.reviewId", "review.likes.id")
            .orderByDesc("SUM(case when reviewLike.positive = true then 1 else 0 end)");

List<Review> reviews = criteriaBuilder.getResultList();

型
我将感谢任何帮助！