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

Can you please answer to my question http://stackoverflow.com/questions/28604309/how-to-write-between-clause-for-from-to-dates-for-createcriteria-in-grails — VVB, Feb 19 '15 at 11:06

score 5 · Accepted Answer · edited Feb 22 '18 at 04:31

5

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.

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

edited Feb 22 '18 at 04:31

Tyler Rafferty

3,391
4
28
37

answered Jan 02 '13 at 09:28

Kumar Sambhav

7,503
15
63
86

I have tried this solution but getting error: could not resolve property: dayOfWeeo of: myClass. what to do? – sanghavi7 May 08 '14 at 08:49
error: org.hibernate.QueryException: could not resolve property: week of: myClass – sanghavi7 May 08 '14 at 08:50
Did you follow the step 2 ? – Kumar Sambhav May 08 '14 at 09:59
yeah, I have provided fields with formula. like : month formula('MONTH(appointment_time)') year formula('YEAR(appointment_time)') day formula('DAYOFMONTH(appointment_time)') – sanghavi7 May 08 '14 at 13:33
Can you please answer to my question http://stackoverflow.com/questions/28604309/how-to-write-between-clause-for-from-to-dates-for-createcriteria-in-grails – VVB Feb 19 '15 at 11:06

score 1 · Answer 2 · answered Aug 27 '14 at 14:00

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

}

score 0 · Answer 3 · answered Dec 29 '12 at 16:00

0

Try something like

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

answered Dec 29 '12 at 16:00

Ian Roberts

120,891
16
170
183

Thanks for prompt response. Now its giving exception:- No such property: NUMBER for class: grails.orm.HibernateCriteriaBuilder The grails doc says the fourth argument should be list of org.hibernate.type.Type How do we adhere to that? – Kumar Sambhav Dec 29 '12 at 16:21

score 0 · Answer 4 · edited May 23 '17 at 12:29

0

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.)

edited May 23 '17 at 12:29

Community

1
1

answered Dec 29 '12 at 16:45

coderLMN

3,076
1
21
26

Simply using groupProperty('dateOfExpense') won't solve the purpose as it will try to group exact date till mill sec level. Approach suggested in http://stackoverflow.com/questions/10455409/grails-group-by-date solved by problem. – Kumar Sambhav Jan 02 '13 at 08:48
Refer to my answer to this question. – Kumar Sambhav Jan 02 '13 at 10:12
Can you please answer to my question http://stackoverflow.com/questions/28604309/how-to-write-between-clause-for-from-to-dates-for-createcriteria-in-grails – VVB Feb 19 '15 at 11:06

Group by week / month / year in GORM

4 Answers4

Linked