using mysql "order by case" in hibernate criteria

Question

I'm using Hibernate 4.3.1 final, Mysql 5.5 and I want to use an "order by case" order logic on some joined entities.

A pure sql representation of what I wish to achieve would look something like:

select adv.id, adv.published_date 
from advert as adv 
  join account as act on act.id = adv.act_id
  join account_status as aas on aas.id = act.current_aas_id
order by case aas.status
when 'pending' THEN 1
when 'approved' THEN 1
else 2
end, adv.published_date;

This orders the adverts of pending and approved accounts before those of deactive accounts.

I've managed to do all the select query using hibernate criteria, but I'm not sure how to specify the order by case statement using that api.

I found this post:

http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/

but I need to reference joined entity classes in the order by case and I'm not sure how to do that.

Any help or suggestions much appreciated.

Answer 1

I think I found a solution.

In the end I created my own subclass of Order and overrode the public String toSqlString(Criteria,CriteriaQuery) method:

@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
    final String[] columns = criteriaQuery.getColumnsUsingProjection( criteria, getPropertyName() );
    final StringBuilder fragment = new StringBuilder();
    fragment.append(" case ").append(columns[0]);
    fragment.append(" when 'pending' then 1 ");
    fragment.append(" when 'approved' then 1 ");
    fragment.append(" else 2 end");
    return fragment.toString();
}

the important call (which I found in the original implementation of the order class) is the

criteriaQuery.getColumnsUsingProjection(criteria, getPropertyName());

Which gave me access to the alias for the column I wanted to order on using the case statement.

If anyone else is looking at this, if you are ordering on a property that is not on the root object, then make sure that you use aliases in your criteria joins and that you then reference those aliases correctly in the your custom Order propertyName when you instantiate it.