Grails 1:m get most relations

Question

I'm relatively new to Grails. I have the following

class House {
    Integer number
    Integer maxResidents

    static belongsTo = [town: Town]
} 

class Town {
    String name

    static hasMany = [houses: House]
}

I want to get five towns with most Houses. I have seen the possibility to create a criteria but I can't deal with it now. Can someone support? Thank you!

Answer 1

As you have a bidirectional association you can do this with a query on House:

def result = House.withCriteria {
  projections {
    groupProperty("town", "town")
    rowCount("numHouses")
  }
  order("numHouses", "desc")
  maxResults(5)
}

This would return you a list of results where each result res has the town as res[0] and the number of houses as res[1]. If you'd prefer each result to be a map giving access to res.town and res.numHouses then you should add

resultTransformer(AliasToEntityMapResultTransformer.INSTANCE)

after the maxResults line (along with the appropriate import at the top of your file).