What are the alternatives to using an ORDER BY in a Subquery in the JPA Criteria API?

Question

Consider the following two tables:

1. Project ( id, project_name)
2. Status ( id, id_project, status_name)

Where Status contains all statuses in which a Project has been.

Lets say we want to query all projects for which the latest status has the name "new". The Sql query that I come up with is:

SELECT q.id_project FROM status q
WHERE q.status_name like 'new'
AND q.id IN (
    SELECT TOP 1 sq.id from status sq
    WHERE q.id_project = sq.id_project 
    ORDER BY sq.id DESC )

I'm trying to replicate the above query using Criteria API and I noticed that the class CriteriaQuery has the method orderBy but the class Subquery doesn't.

The Criteria Query that I have come up with so far is:

CriteriaQuery<Project> q = criteriaBuilder.createQuery(Project.class);
Root<Status> qFrom       = q.from(Status.class);
Subquery<Integer> sq     = q.subquery(Integer.class);
Root<Status> sqFrom      = sq.from(Status.class);

sq.select(sqFrom.get(Status_.id))
  .where(criteriaBuilder.equal(sqFrom.get(Status_.project), qFrom.get(Status_.project))

I got stuck here because Subquery sq doesn't have any method for sorting its results and returning only the latest one.

What are the alternatives to sorting the subquery in order to get the desired result in the scenario described above?

Answer 1

The subquery could be written using SELECT MAX instead of SELECT TOP 1 ... ORDER BY ...:

SELECT q.id_project FROM status q
WHERE q.status_name like 'new'
AND q.id = (
    SELECT MAX(sq.id) from status sq
    WHERE q.id_project = sq.id_project)

This would also be faster as ordering all records is slow compared to finding a maximum value. As already posted, it can be translated into a CriteriaQuery.

Unfortunately Criteria Queries do not support ordering in a subquery - see these related answers:

https://stackoverflow.com/questions/19549616#28233425 https://stackoverflow.com/questions/5551459#5551571

An alternative if you really need an ORDER BY (e.g. to allow a range of values in the example below) is to use a native query instead of a CriteriaQuery:

Query query = entityManager.createNativeQuery(
    "SELECT bar FROM foo WHERE bar IN (SELECT TOP 10 baz FROM quux ORDER BY quuux)");