CODEWITHSUNDEEP
grailsgrails-orm
Kumar Sambhav
Kumar Sambhav

Reputation: 7765

Group by week / month / year in GORM

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

Upvotes: 1

Views: 5761

Answers (4)

Kumar Sambhav
Kumar Sambhav

Reputation: 7765

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

Upvotes: 5

Héctor Gallego
Héctor Gallego

Reputation: 11

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

}

Upvotes: 1

coderLMN
coderLMN

Reputation: 3076

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

Upvotes: 0

Ian Roberts
Ian Roberts

Reputation: 122364

Try something like

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

Upvotes: 0

Related Questions