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?

Pocosin

String-based JPQL does not allow ORDER BY in a subquery, and Criteria simply mirrors that. "subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause]" – Lodmilla

@NeilStockton I understood that, which is why I am asking for alternative solutions – Pocosin

so you want to return the highest value of "sq.id" from the subquery (subject to the where clause)? can't you just do a subquery of "SELECT MAX(sq.id) FROM status sq WHERE q.id_project = sq.id_project" ? – Lodmilla

@NeilStockton You are right. Don't know why I didn't think of that. Please post it as an answer and I will accept it. – Pocosin

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)");

Apparent

Answer 2

Your subquery, as you have stated it, would equate to JPQL of

SELECT MAX(sq.id) FROM status sq WHERE q.id_project = sq.id_project

which ought to be fully supported by the JPA spec. In terms of Criteria API, I'd guess at this

Subquery<Integer> sq     = q.subquery(Integer.class);
Root<Status> sqFrom      = sq.from(Status.class);
Expression maxExpr = criteriaBuilder.max(sqFrom.get(Status_.id));
sq.select(maxExpr).where(criteriaBuilder.equal(sqFrom.get(Status_.project), qFrom.get(Status_.project))

Lodmilla

Answer 3

In addition to other answers, I don't think that database optimizer is able to implicitly rewrite a correlated subquery with aggregate functions to an uncorrelated form, so I think that writing an uncorrelated subquery explicitly is better performance-wise (below example uses JPQL, Criteria API equivalent should be straightforward):

select q.project from Status q
where q.name = 'new'
  and q.id in (select max(sq.id) from Status sq group by sq.project.id)

Clamworm