Search code examples
mysqlgrailshqlcriteria

Grails Criteria - multiple functions on same field

I know I can use a mysql function in Criteria as follows:

Number rating = Book.createCriteria().get
      eq("author", author)
      projections {
          max rating
      }
}

to execute the equivalent of 

select max(rating) from book where author = 'authors name';

But if this rating can be positive or negative, and I want the absolute maximum, is it possible to execute the following in Criteria:

select max(Abs(rating)) from book where author = 'authors name'

Maybe I need to revert to HQL but just wanted to see if this was something I could do first.

Solution

  • Personally, I'd rewrite it as HQL

    def result = Book.executeQuery(
    "select max(abs(rating)) from Book where author = :author", [author: author])

    You can use SQL in the predicates of a criteria query via sqlRestriction, but I don't think it's possible to use them in projections.