Search code examples
grailsgrails-ormcriteriagrails-3.3

Grails 3.3 GORM CriteriaBuilder functions on properties

I'm using Grails 3.3 and GORM CriteriaBuilder for most of my querying these days but I am stuck on how to call functions on properties - for example, calling the hour() function on a Date property. If I were using where DetachedCriteria I could have something like this:

def whereQuery = Student.where {
    hour(registration) = 15
}

to find all students who registered between 15:00 and 15:59.

However, if I use a CriteriaBuilder instead, I cannot do this:

def c = Student.createCriteria()
def results =  c.list {
    eq 'hour(registration)', 15
}

Is there some way to accomplish this in the builder DSL? I know I can go back to the domain class and define a mapping that extracts the hour from the date field but that seems kind of clumsy.

Solution

  • Give the sqlRestriction a try:

    def results = Student.withCriteria {
    sqlRestriction 'hour(registration) = 15'
}

    See 7.5.6. Using SQL Restrictions in http://gorm.grails.org/latest/hibernate/manual/index.html#criteria