0

Domain Class:

class Record {
    Date date
    Integer days
}

Code:

def record.createCriteria().list {
    order( "DATE_ADD( date, INTERVAL days DAY )", "asc" )
}

Question:

Obviously this doesn't work, but I need to know if there is a way to order by date + days using createCriteria.

Thank you :)

havoc74
  • 33
  • 5
  • I don't think this will be possible using `createCriteria` as the `order` node is really just a wrapper around the `org.hibernate.criterion.Order` class, which doesn't have this capability. I'm sure there is a SQL only solution but I don't believe there is an easier way through GORM. – tylerwal Dec 02 '15 at 01:27
  • You can use @Formula mapping and place your expression in a field (let's name it sortOrder) of Record and later just use the field in order() – StanislavL Dec 02 '15 at 06:55

1 Answers1

0

Like what @StanislavL said, you can use formula

Date dateWithAdditionalDays

static mapping = {
        dateWithAdditionalDays formula: 'DATE_ADD( date, INTERVAL days DAY )'
}

def record.createCriteria().list {
    order('dateWithAdditionalDays')
}
renz
  • 1,072
  • 3
  • 11
  • 21
  • According to your link, `formula` is only for use under the `discriminator` mapping and its purpose is to : "**an SQL expression that is executed to evaluate the type of class.**" – tylerwal Dec 02 '15 at 14:30
  • I found the proper documentation: [Derived Properties](https://grails.github.io/grails-doc/3.0.x/guide/GORM.html#derivedProperties) – tylerwal Dec 02 '15 at 14:32
  • So this is a great idea, but fails with:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'this_.INTERVAL this_.days this_.DAY ) as formula0_0_ from record this_ order by ' at line 1 – havoc74 Dec 02 '15 at 14:33
  • Thanks @tylerwal. havoc74, is your formula working in your mySql database? – renz Dec 03 '15 at 06:28