Grails how to sort list by children max date using criteria

user_2856694 0
Viewed 116

I'm using grails (version 3.1.5) and trying to sort a list by the max date of a children class

Domain:

class Entity {
    static hasMany = [childrens: Children]
}

class Children {
    Date date
}

Something like this in SQL:

SELECT 
    *
FROM 
    entity
INNER JOIN 
    children c1 ON c1.entity_id = entity.id
WHERE 
    c1.date = 
        (SELECT MAX(c2.date) FROM children c2 WHERE c2.entity_id = c1.entity_id) 
ORDER BY 
    c1.date DESC

Does anyone know how to do this using criteria?

EDIT:

I was able to execute the right query with the code below but I couldn't transform the result in a list of Entities (because of the projections):

Entity.createCriteria().list(params) {
    childrens {
        projections {
            groupProperty 'entity'
            max 'date', 'maxDate'
        }
    }
    order 'maxDate', 'desc'
}
grails-orm grails
1 Answers

The easiest way to solve this kind of issues is to add some redundnancy:

class Entity {
    Date maxChildrenDate

    def beforeInsert() {
      maxChildrenDate = childrens.*date.max()
    }

    def beforeUpdate() {
      beforeInsert()
    }

    static hasMany = [childrens: Children]
}

Then the querying is straight-forward:

def list = Entity.list( sort:'maxChildrenDate', order:'asc' )

To set/update the maxChildrenDate you might want to use interceptors shown above.

user_16828200
Related