[FIXED] Criteria API Ordering by nested aggregated property

Issue

How do I implement ordering objects by aggregated nested property? I have Photographer entity which one has a lot of PhotographerPrice entities (One to Many) with BigDecimal property called pricePerHour. When I retrieving photographers I want to sort them by the minimal price of whole prices they have.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Photographer> cq = cb.createQuery(Photographer.class);
Root<Photographer> root = cq.from(Photographer.class);
List<Predicate> predicates = new ArrayList<>(); // I have a lot of predicates which set if data was present by client

I tried to make a subquery to PhotographerPrice and than sort in root

Subquery<BigDecimal> subquery = cq.subquery(BigDecimal.class);
Root<PhotographerPrice> from = subquery.from(PhotographerPrice.class);
Predicate and = cb.and(
        cb.equal(root.get(Photographer_.id), from.get(PhotographerPrice_.photographer).get(Photographer_.id)),
        cb.isNotNull(from.get(PhotographerPrice_.pricePerHour))
);
subquery.correlate(root);
subquery.where(and);
subquery.select(cb.min(from.get(PhotographerPrice_.pricePerHour)));
subquery.groupBy(from.get(PhotographerPrice_.photographer).get(Photographer_.id));

...

cq.orderBy(cb.asc(subquery));

But, as I realized, it's not allowed to use a subquery in order by clause.

So, how do I can implement something like this using Criteria API:

select *
  from photographer p
  order by (
    select min(price_per_hour) minPrice
      from photographer_price pp
      where p.id = pp.photographer_id
        and pp.photo_per_hour is not null
      group by photographer_id
    );

When I tried to implement it with Join approach I've got duplicates in my result list.

Is it possible to implement it using Criteria API? Maybe there is another tool to make filtering for entities from DB more convenient? I have a lot of different parameters for filtering and sorting which related to nested properties, sometimes even related to nested in a nested property.

The only way I found to solve it:

ListJoin<Photographer, PhotographerPrice> join = root.join(Photographer_.photographerPrices);
Expression<BigDecimal> min = cb.min(join.get(PhotographerPrice_.pricePerHour));
cq.orderBy(cb.desc(min));
cq.groupBy(root.get(Photographer_.id));

But I don't sure about group by. Is it possible some troubleshooting appear later?

Solution

The approach I found that works in my case

Do left join to PhotographerPrice with Min aggregate function after that make an order based by result of this aggregate:

ListJoin<Photographer, PhotographerPrice> photographerPriceJoin = root.join(Photographer_.photographerPrices);
Expression<BigDecimal> min = cb.min(photographerPriceJoin.get(PhotographerPrice_.pricePerHour));
if (photographerCatalogFilter.getDirection().isDescending()) {
    orderList.add(cb.desc(min));
} else {
    orderList.add(cb.asc(min));
}

Answered By - Anton Bogatikov