Ordering results of a JPA CriteriaQuery by MAX(childitem.property)

Question

I'm trying to find an elegant way of sorting the results of a JPA criteria query using the properties of the first of a collection of child object rather than with the object itself. E.g:

@Entity
class Parent {
  protected Set<Child> children;
}

@Entity
class Child {
  protected Date timestamp;
}

A parent can have several children each with a different timestamp.

I want to be able to add to an existing JPA CriteriaQuery/TypedQuery so that sorts the list of Parent objects by MAX(child.timestamp), so the parent with the latest child timestamp appears first.

My general-purpose criteria builder implementation is like this:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(daoClass);
Root<T> queryRoot = criteriaQuery.from(daoClass);

// add wheres
Predicate filters = createPredicate(criteriaQuery, queryRoot, criteriaBuilder);
// filters = criteriaBuilder.and( /* lots of Predicate filter objects */ );
criteriaQuery.where(filters);

// add sorts
criteriaQuery.orderBy(createOrders(queryRoot, criteriaBuilder));

// <-- Ideally somewhere here add the new ordering

TypedQuery<T> typedQuery = em.createQuery(criteriaQuery);

Hopefully there is a solution that won't break it too much..

Thanks for any help.

EDIT: The Parent data that is gathered in the query is paged, so I can't sort the results of the query. It needs to be part of the JPA query I construct.

score 0 · Answer 1 · answered Jan 08 '14 at 16:47

0

Can't you do

@Entity
class Parent {
  @OrderBy("timestamp DESC")
  protected Set<Child> children;
}

answered Jan 08 '14 at 16:47

ZeusSelerim

935
8
12

This would only sort the children by a timestamp for a single parent object. What I want is for the List to be in the order of the latest child timestamp. Example: Parent A has Child1.timestamp of 10am and Child2.timestamp of 11am. Parent B has Child3.timestamp of 9am and Child4.timestamp of 4pm. Parent C has Child5.timestamp of 12pm. I want a CriteriaQuery configuration that will return a List in the order [Parent B, Parent C, Parent A]. – fancyplants Jan 09 '14 at 09:07

score 0 · Answer 2 · edited May 23 '17 at 12:12

I ran into the same situation.

See JPA CriteriaQuery Order By Latest Child Property

For your specific case, the trick is not to add the filters before the order by part. The Criteria API will put in in the correct order later.

So it should look like this:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(daoClass);
Root<T> queryRoot = criteriaQuery.from(daoClass);

// get filters
Predicate filters = createPredicate(criteriaQuery, queryRoot, criteriaBuilder);
// filters = criteriaBuilder.and( /* lots of Predicate filter objects */ );
//This has to be a Boolean object (not primitive) so that it passes the reference to the object to your createOrders method and not the value.
Boolean filtersAdded = Boolean.FALSE;

// add sorts - send filters as a parameter
criteriaQuery.orderBy(createOrders(filters, filtersAdded, queryRoot, criteriaBuilder));

if (Boolean.FALSE.equals(filtersAdded)) {
    criteriaQuery.where(filters);
}

TypedQuery<T> typedQuery = em.createQuery(criteriaQuery);

In the createOrders method, if you have to add a subquery where condition (JPA CriteriaQuery Order By Latest Child Property), add the filters as well. Like so:

criteriaQuery.where(subQueryCondition, filters));
filtersAdded = Boolean.TRUE;

Ordering results of a JPA CriteriaQuery by MAX(childitem.property)

2 Answers2