0

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.

MonetsChemist
  • 165
  • 10

1 Answers1

1

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

injecteer
  • 20,038
  • 4
  • 45
  • 89
  • Excellent suggestion, injecteer, with a slight modification. Using PostgreSQL as my back end, rather than using the Hibernate 'hour' function, I had to use this: `sqlRestriction 'extract(hour from registration) = 15'` – MonetsChemist Jun 01 '18 at 18:58