Grails Self-Ad Criteria

Question

Grails Self-Ad Criteria

The project I am working on has a part of the database that looks like the following diagram

Domain classes have a definition similar to the following:

class File{
    String name
}

class Document{
    File file
}

class LogEntry{
    Document document
    Date date
}

First I need to get only the latest version of LogEntry for all documents; in SQL, I do the following (SQL_1):

SELECT t1.* FROM log_entry AS t1 
LEFT OUTER JOIN log_entry t2 
on t1.document_id = t2.document_id AND t1.date < t2.date 
WHERE t2.date IS NULL

Then in my service I have this function:

List<LogEntry> logs(){
    LogEntry.withSession {Session session ->
        def query =  session.createSQLQuery(
                """SELECT t1.* FROM log_entry AS t1 
                    LEFT OUTER JOIN log_entry t2 
                    on t1.document_id = t2.document_id AND t1.date < t2.date 
                    WHERE t2.date IS NULL"""
        )
        def results = query.with {
            addEntity(LogEntry)
            list()
        }
        return results
    }
}

The SQL query really solves my problem, at least in some way. I also need to paginate, filter and sort my results, as well as join LogEntry, Document, and File tables. Although this is possible in SQL, it can be quite complex.

In another project, I usedQuery criteria similar to the following:

Criteria criteria = LogEntry.createCriteria()
criteria.list(params){ //Max, Offset, Sort, Order
    fetchMode 'document', FetchMode.JOIN //THE JOIN PART
    fetchMode 'document.file', FetchMode.JOIN //THE JOIN PART

    createAlias("document","_document") //Alias could be an option but I would need to add transients, since it seems to need an association path, and even then I am not so sure

    if(params.filter){ //Filters
        if(params.filter.name){
            eq('name', filter.name)
        }
    }
}