How to create orderby on non-entity field in Criteria Query?

Question

I want to order by on a field known at runtime. Following is the simplified SQL query which I'm trying to convert in Criteria Query:

SELECT 
CASE
    WHEN o.col2 > 0 THEN "START"
    WHEN o.col2 < 0 THEN "STOP"
END AS STATUS,
o.*
FROM orders o 
JOIN trade t
ON t.ID = o.t_id
WHERE t.id='1'
ORDER BY STATUS;

Following is what I've achieved so far:

CriteriaBuilder cb = getCriteriaBuilder();
CriteriaQuery<Orders> cq = cb.createQuery(Orders.class);
Root<Orders> oRoot = cq.from(Orders.class);
Join<Orders, Trade> tradeJoin = oRoot.join(Orders_.trade);
Expression<String> start = cb.literal("START");
Expression<String> stop = cb.literal("STOP");
Expression<String> statusExpr = cb.selectCase()
        .when(cb.greaterThan(oRoot.get(Orders_.someCol2), 0), start)
        .when(cb.lessThan(oRoot.get(Orders_.someCol2), 0), stop)
        .otherwise(oRoot.get(Orders_.someCol2))
        .as(String.class);
cq.multiselect(statusExpr.alias("status"), oRoot);
cq.where(cb.equal(tradeJoin.get("id"), tradeId));
//some code to fetch the sorting details
//...
cq.orderBy(cb.desc(cb.literal(sortStr)));//assume sortStr = "status"
return em.createQuery(cq).getResultList();

When I checked the query in hibernate logs, I found that the alias "status" is not getting assigned to `statusExpr` instead hibernates' autogenerated alias is getting created. This is making the above query fail by not returning the data in said order.

Any help would be appreciated.

@alexvaluiskyi I can use this when I only need to sort on the basis of "statusExpr" but the column on which I need to sort is identified at run time and it can be any column from oRoot plus status. — Diksha, Jan 21 '21 at 04:27

Oleksii Valuiskyi · Accepted Answer · 2021-01-21T06:11:04.867

The problem is that order by operation performed before select. At order by point in time aliases are not existed.

To order by a calculated field the query has to be like this

SELECT 
   CASE
      WHEN o.col2 > 0 THEN "START"
      WHEN o.col2 < 0 THEN "STOP"
   END,
   o.*
FROM orders o 
JOIN trade t
ON t.ID = o.t_id
WHERE t.id='1'
ORDER BY 
   CASE
      WHEN o.col2 > 0 THEN "START"
      WHEN o.col2 < 0 THEN "STOP"
   END;

So the solution

Expression<?> sortExpression;

if(sortStr.equalsIgnoreCase("status")) {
    sortExpression = statusExpr;
} else {
    sortExpression = oRoot.get(sortStr);
}

//...

cq.orderBy(cb.desc(sortExpression));

For multiple calculated fields you can use Map<String, Expression<?>> aliasExpressionMap = new HashMap<>(); Put calculated expressions into it and then

Expression<?> sortExpression = 
    aliasExpressionMap.getOrDefault(sortStr, oRoot.get(sortStr));

//...
    
cq.orderBy(cb.desc(sortExpression));

+1 for "The problem is that order by operation performed before select. At order by point in time aliases are not existed." I didn't know this. Thanks. — Diksha, Jan 21 '21 at 06:34

How to create orderby on non-entity field in Criteria Query?

1 Answers1