hibernate - Grails with Criteria -

i want use nvl function in grails criteria orderby clause. how shall used? have tried multiple approach. can me out ?

sql query converted grails criteria : select * forom domain_table order nvl(field1,field2) asc

tried approach 1:

domain.createcriteria().list(max:10,offset:10){    order(field1,'asc')    order(field2,'asc') } 

working properly, generating sql query

select * forom domain_table order field1 asc,field2 asc 

which not satisfying requirement

approach 2 :

 domain.createcriteria().list(max:10,offset:10){        order(nvl(field1,field2),'asc')     } 

error : nvl not property of domain class

approach 3 :

domain.createcriteria().list(max:10,offset:10){    projections{   addprojectiontolist(projections.sqlprojection("nvl(field1,field2) description", ['description'] string[], [hibernate.string] type[]), 'description'))            order('description,'asc')         } 

problem : getting record based on exact order . but, again hitting database description domain don't want. 1 more option there, can put property(name,alias) i'll in response. but, need entire domain queried.

please me out.

you can use @formula artificial field on entity (let's name nvl) , use in ordering.

or can use custom implementation of order

public class orderbysqlformula extends order {     private string sqlformula;      /**      * constructor order.      * @param sqlformula sql formula appended resulting sql query      */     protected orderbysqlformula(string sqlformula) {         super(sqlformula, true);         this.sqlformula = sqlformula;     }      public string tostring() {         return sqlformula;     }      public string tosqlstring(criteria criteria, criteriaquery criteriaquery) throws hibernateexception {         return sqlformula;     }      /**      * custom order      *      * @param sqlformula sql formula appended resulting sql query      * @return order      */     public static order sqlformula(string sqlformula) {         return new orderbysqlformula(sqlformula);     } } 

and use criteria.addorder(orderbysqlformula.sqlformula("(a + b) desc"));

from here

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