How to get an average of a hasMany instance property in grails domain classes?

1
votes

I have two classes in Grails application ,Employee has hasMany(one To Many relations) for another class called Vote

class Employee {
   String employeeName   
   static hasMany = [votes:Vote]
}

and vote class has voteRank integer property, and all employees vote on scale of 1-10 and every employee contains list of these votes given by other employees

class Vote {
   Integer voteRank
   static belongsTo =[employee:Employee]
}

How do I get the avg of all votes given per employee. This can be solved in SQL by using group by clause. But i am looking for grails domain mapping and gorm oriented answer for this problem.

2
grailsgrails-orm

2 Answers

4
votes

This HQL query will work:

def averages = Employee.executeQuery(
    'select e, avg(vote.voteRank) ' +
    'from Employee e join e.votes vote ' +
    'group by e')

This will return a List of Object[] arrays where the first element is the Employee and the second is the average. If you don't want to return the whole Employee instances you can select one or more properties: 

def averages = Employee.executeQuery(
    'select e.employeeName, avg(vote.voteRank) ' +
    'from Employee e join e.votes vote ' +
    'group by e')
3
votes

You can accomplish this with a simple method on the Employee domain class.

class Employee {
    String employeeName
    static hasMany = [votes: Vote]

    def sumVotes() {
        votes.voteRank.sum() / votes.size()
    }
}