ORDER BY in Criteria API for a computed column name (by alias)

Having a situation where my java code is symbolic to query -

  SELECT CUSTOMER_ID,
CUSTOMER_NAME,
CASE
WHEN COUNT (DISTINCT CARD_ID) > 1 THEN 'MULTIPLE'
ELSE MAX(CARD_NUM)
END    AS CARD_NUM
FROM CUSTOMER LEFT JOIN CARD ON CARD.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
GROUP BY CUSTOMER_ID, CUSTOMER_NAME

Java code for detailed info -

CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Tuple> query = cb.createQuery(Tuple.class);
final Root<Customer> root = query.from(Customer.class);
Expression<Object> caseSelect = cb.selectCase()
.when(cb.greaterThan(cb.countDistinct(join.get(Card_.cardId)), 1L), "MULTIPLE")
.otherwise(cb.greatest(Card_.get(Card_.cardNum)));
caseSelect.alias("card_num");
selects.add(caseSelect);
query.multiselect(selects).distinct(true);
query.groupBy(exprs);
query.orderBy(cb.asc(caseSelect));

Now, how to do the order by in Criteria API.

1. If I do orderby root.CARD_NUM, the attribute is not present in Root.- Throws exception.
2. If I do order by the caseSelect expression itself it throws an error
   stating CARD_ID is not in SELECTED part of query.
3. I cannot have card_num / card_id in select expression, that is not right for the query.

Any way to just order by Alias name? I see Order is Expression type, and how to get an expression from string name. I guess you can do this in Hibernate. Would it be possible to use hibernate orderby in criteria API anyways ? Guess a stupid q

Any help is appreciated.

I faced a similar situation...

query.multiselect(root, computedColumn);
query.orderBy(new Order[]{filterDTO.getSortAsc() ? cb.asc(cb.literal(2)) : cb.desc(cb.literal(2))});

I my case computedColumn is Subquery<String>...I did not manage to make it work by column alias but it seems to work by column index returned in the tupple so I guess in your code it should work by index 1

query.orderBy(cb.asc(cb.literal(1)));

Using the Criteria API, you need to order by the caseSelect expression. I gave it a try and it works fine with Hibernate 5.4. Which version do you use?

It seems this is not possible with the JPA Criteria API and you will have to fallback to using JPQL/HQL instead.

This is fairly not possible and just feels like a case missed by JPA.
Though if using hibernate API it is possible. But, my workaround was -

1. Created a view which would contain the case expression.
2. Join the view with my entity (you cannot do a join, but one more
   query.from(View.class)).
3. In the where add the ids of View and the entity.

Now in the order by you could mention the column name by View.column_name.