Ordering JPA statement with SUM function

[Rubynam]

Hi team, I am using Spring Boot 3.2.4 with Spring Data JPA. I have the following JpaRepository and Specification with a CriteriaBuilder query against MYSQL. JPA/Hibernate always generate A SUM function into Ordering statement. My code below:

return (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
            query.distinct(true);
            query.groupBy(root.get(USER_ID));
            Expression<Long> reportCount = builder.count(root.join(REWARDS, JoinType.LEFT).get(REWARD_ID));

            Join<Object, Object> joinReward = root.join(REWARDS, JoinType.LEFT);
            joinReward = joinReward.on(builder.equal(joinReward.get(REWARD_STATUS),RewardStatusEnum.PAID.getId()));

            Expression<Double> totalAmount = builder.sum(joinReward.get(REWARD_COLUMN));

            query = query.multiselect(root, reportCount, totalAmount);

            query.where(predicate);
             query.orderBy(builder.desc(totalAmount));
            return query.getRestriction();
        };

Hibernate query debug:

select distinct u1_0.*,
from user u1_0
    left join rewards r1_0 on u1_0.id=r1_0.reporter_id
    left join rewards r2_0 on u1_0.id=r2_0.reporter_id and r2_0.status=?
group by user_id
order by sum(r2_0.reward) desc limit ?,?

My question that: Is it possible when i try to move a SUM function to selection statement?

My expectation:

select distinct u1_0.*, sum(r2_0.reward)) as total
from user u1_0
    left join rewards r1_0 on u1_0.id=r1_0.reporter_id
    left join rewards r2_0 on u1_0.id=r2_0.reporter_id and r2_0.status=?
group by user_id
order by total desc limit ?,?

[mp911de]

That's achievable via query.orderBy(cb.asc(cb.literal(1))) but you need to mind property ordering. In any case, as this topic relates to the JPA API and not Spring Data, I'm closing the ticket.