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

This sqlGroupProjection method seems to be poorly supported. Use

def results = c.list {
    between("dateOfExpense", fromDate, toDate) 
    projections {
        groupProperty('dateOfExpense')
        sum('amount')
    }
}

will produce the deserved outcome.

If you want group by the month of the date, see Grails group by date (It totally outweight my answer, actually. But I reach the same solution after trying your code for a long time.)

Answer 2

Try something like

sqlGroupProjection 'MONTH(dateOfExpense) as month, sum(amount) as summed',
    'month',['month','summed'],[NUMBER,NUMBER]

Answer 3

Instead of this

static mapping = {

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

}

try this

static mapping = {

week formula: 'WEEK(DATE)'    
month formula: 'MONTH(DATE)'
year formula: 'YEAR(DATE)'

}

Answer 4

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')
  }
}