CODEWITHSUNDEEP
grailshql
Kurent
Kurent

Reputation: 99

HQL order by max item in child list

I have the following domain classes 

class Child{
    def age
}

class Parent{
    static hasMany = [children:Child]
}

and I would like to execute the following in HQL

Parent.list()
    .sort{ parent -> parent.children.sort{ child -> child.age}[0]}[0..10]

Basically I would like to retrieve a list of parents sorted by the age of their eldest child. And restrict this to only 10 records. I don't want to pull all parent and child records from the database, and then do the necessary sorting. I was hoping that HQL could do this on the 'database layer', and only return the results I need. Thanks :)

Upvotes: 0

Views: 277

Answers (1)

jenk
jenk

Reputation: 1043

Parent.withCriteria {
        createAlias('children', 'ch', org.hibernate.criterion.CriteriaSpecification.LEFT_JOIN)
        order("ch.age", "desc")
        maxResults(10)

        setResultTransformer(org.hibernate.criterion.CriteriaSpecification.DISTINCT_ROOT_ENTITY)
    }

Upvotes: 1

Related Questions