CODEWITHSUNDEEP
grailsgrails-orm
Odinodin
Odinodin

Reputation: 2177

Grails group by date

I have a domain class with a date-property. 

class Transaction {
    LocalDate time
    BigDecimal amount
}

How can I query for the sum of all transactions grouped by month? I can´t find any support for group by a date-range in GORM.

Upvotes: 6

Views: 5265

Answers (1)

ataylor
ataylor

Reputation: 66069

Add a formula based field to your domain class for the truncated date:

class Transaction {
    LocalTime time
    BigDecimal amount
    String timeMonth

    static mapping = {
        timeMonth formula: "FORMATDATETIME(time, 'yyyy-MM')" // h2 sql
        //timeMonth formula: "DATE_FORMAT(time, '%Y-%m')"   // mysql sql
    }
}

Then you'll be able to run queries like this:

Transaction.withCriteria {
    projections {
        sum('amount')
        groupProperty('timeMonth')
    }
}

Upvotes: 18

Related Questions