Quelles sont les alternatives à l'utilisation d'un ORDER BY dans une sous-requête dans l'API JPA Criteria?

Considérez les deux tables suivantes:

Project ( id, project_name)
Status ( id, id_project, status_name)

Où Status contient tous les statuts dans lesquels un Project a été.

Disons que nous voulons interroger tous les projets pour lesquels le dernier statut a le nom "nouveau". La requête Sql que je viens avec est:

 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 )

J'essaie de répliquer la requête ci-dessus en utilisant l'API Criteria et j'ai remarqué que la class CriteriaQuery a la méthode orderBy mais pas la class Subquery .

La requête de critères que j'ai trouvé jusqu'à présent est:

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

Je me suis coincé ici parce que Subquery sq n'a aucune méthode pour sortinger ses résultats et returnner seulement le dernier.

Quelles sont les alternatives au sorting de la sous-requête pour get le résultat souhaité dans le scénario décrit ci-dessus?

La sous-requête peut être écrite en utilisant SELECT MAX au lieu de 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)

Cela serait également plus rapide car la command de tous les loggings est lente par rapport à la search d'une valeur maximale. Comme déjà posté, il peut être traduit en une CritèreQuery.

Malheureusement, les requêtes de critères ne prennent pas en charge la command dans une sous-requête. Reportez-vous aux réponses suivantes:

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

Une alternative si vous avez vraiment besoin d'un ORDER BY (par exemple pour autoriser une plage de valeurs dans l'exemple ci-dessous) est d'utiliser une requête native au lieu d'une requête CriteriaQuery:

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

Votre sous-requête, comme vous l'avez indiqué, équivaudrait à JPQL de

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

qui devrait être entièrement pris en charge par la spécification JPA. En termes d'API Criteria, je devinerais à ce

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

En plus des autres réponses, je ne pense pas que l'optimiseur de database puisse réécrire implicitement une sous-requête corrélée avec des fonctions agrégées sous une forme non corrélée, donc je pense qu'écrire explicitement une sous-requête non corrélée est plus performant. L'équivalent de l'API des critères devrait être simple):

 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)