I am trying to find a workaround to the fact that JPA doesn't support subquery in order by.
How can I implement the following query using CriteriaBuilder?
@Query(value =
"SELECT DISTINCT t,(SELECT sum(ta.size) FROM TaskArea ta WHERE ta.task.id = t.id) as total " +
"FROM Task t " +
"ORDER BY total")
List<Tuple> ordered();
I have try to do the following but it doesn't work:
public List<Tuple> ordered() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
Root<Task> root = cq.from(Task.class);
Subquery sub = cq.subquery(Long.class);
Root<TaskArea> subRoot = sub.from(TaskArea.class);
sub.select(cb.sum(subRoot.get(TaskArea_.size)));
sub.where(cb.equal(root.get(Task_.id), subRoot.get(TaskArea_.task).get(Task_.id)));
sub.alias("total");
cq.multiselect(root, sub.getSelection());
cq.orderBy(cb.asc(sub)); // duplicate the subquery into order by section instead of use the alias
// cq.orderBy(cb.asc(cb.literal("total"))); // not working
return em.createQuery(cq).getResultList();
}
Is there a way to tell CriteriaBuilder to use the alias instead of duplicate the subquery?
(Please do not suggest rewrite the query using joins etc - the real case it much complex and it can't be done easily or at all using joins)