Group by week / month / year in GORM

Question

I have a domain class (minified) as :-

class Expense {
    Date dateOfExpense
    int amount
}

I am trying to get sum of amount grouped by week/month/ year of expense date. Referring to 'sqlGroupProjection' method in grails doc http://grails.org/doc/latest/guide/GORM.html,

I tried using following code:-

def results = c {
    between("dateOfExpense", fromDate, toDate)              
    projections {
         sqlGroupProjection 'dateOfExpense,sum(amount) as summed',       
        'MONTH(dateOfExpense)',['date','summed'],[DATE,NUMBER]                  
    }
}

Throws exception:

 No such property: DATE for class: grails.orm.HibernateCriteriaBuilder. Stacktrace follows:
 Message: No such property: DATE for class: grails.orm.HibernateCriteriaBuilder

Please suggest an approach using sqlGroupProjection method

Answer 1

1. Create three new numeric fields each for week,month and year in the domain class. These fields won't be mapped to column in the table.

2. Provide static mapping for the three fields.

   static mapping = {
        //provide the exact column name of the date field
        week formula('WEEK(DATE_OF_EXPENSE)')    
        month formula('MONTH(DATE_OF_EXPENSE)')
        year formula ('YEAR(DATE_OF_EXPENSE)')
   }
   

Now we can group by desired field using

def results = c.list {
  between("dateOfExpense", fromDate, toDate) 
  projections {
    switch(groupBy){
        case "week":
           groupProperty('year')
           groupProperty('month')
           groupProperty('week') 
        break;
        case "month"
           groupProperty('year')
           groupProperty('month')
        break;
        case "year":
           groupProperty('year')
        break;
    }        
    sum('amount')
  }
}