Non-Trivial Data-Query in Grails

imagine the following problem in grails

you have some kind of audit trail domain class with numeric properties. For instance a class in which the current burndown value of your scrum project is written:

class burndown {
    Date createDate
    int  value
}

Your projects uses this class to store the current burndown value each time you update a task - this means several times a day.

Now you want to plot a diagram with the last stored value for each day.

An SQL statement for this could look something like

select 
  * 
from 
  table 
where 
  id in (
    select 
      max(id) 
    from 
      table 
    group by 
      TO_CHAR(create_date,'yyyyddmm')
  )

Now my question: how do you do such a query in grails?

If I have to use such an SQL statement, how to I avoid to put the table and column names hard coded in the statement?

PS: this code hasn't been tested. just wri开发者_StackOverflowtten down from my mind... but I guess you feel what I want to ask

---

For starters you'll most likely want to rename createDate to dateCreated since it's automatically set for you by Grails if you use that name, so you only need to specify values for the 'real' properties of the class. It's not important to this issue though.

If you want the most recent item by created date, there are a few different ways you could do this but I think this makes the most sense:

def mostRecent = Burndown.listOrderByDateCreated(max: 1, order: 'desc')[0]

or if you retain your name

def mostRecent = Burndown.listOrderByCreateDate(max: 1, order: 'desc')[0]

This is described at http://grails.org/doc/latest/ - it's a fine manual, worthy of reading.

---

Not a Grails user, so below should be corrected accordingly by a Grails'ist

If domains are related, try something like:

Foo.findAllInList(Bar.list([group:'create_date']))