https://twitter.com/teshi04/status/354805661596516352

gistfile1.md

https://twitter.com/teshi04/status/354805661596516352

select person, sum(hours) from table where no = '1234' group by person

みたいなのをgrailsで

↓のドメインクラスを作って

package teshi

class Table {

    Long no
    String person
    Integer hours

    static constraints = {
    }
}

↓をgrails consoleで試すと良い。

import teshi.*

Table.list()*.delete(flush:true)

(1..3).each { hours ->
println "*"
    new Table(no:1234L, hours:hours, person:"me").save(faileOnError: true)
    new Table(no:1234L, hours:hours, person:"your").save(faileOnError: true)
    new Table(no:9999L, hours:hours, person:"me").save(faileOnError: true)
}

Table.list().each {
    println "${it.no}:${it.person}:${it.hours}"
}

def result = Table.withCriteria {
    projections {
        sum('hours')
        groupProperty("person")
        rowCount()
    }
    eq 'no', 1234L
}

println result

結果は

9999:me:3
1234:your:3
1234:me:3
9999:me:2
1234:your:2
1234:me:2
9999:me:1
1234:your:1
1234:me:1

[[6, your, 3], [6, me, 3]]

ちなみにSQLはこうなります。H2の場合:

    select
        sum(this_.hours) as y0_,
        this_.person as y1_,
        count(*) as y2_ 
    from
        table this_ 
    where
        this_.no=? 
    group by
        this_.person

あ、rowCount / count(*)はデバッグのために入れたので実際には不要です。