5

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)

Ran
  • 462
  • 7
  • 15
  • Any luck on how to do this ? In a similar situation. https://stackoverflow.com/questions/63082972/order-by-in-criteria-api-for-a-computed-column-name-by-alias – user3499836 Jul 26 '20 at 08:45

0 Answers0