Grails Avoid N+1 in hasMany case

Question

I have two Domains: Chapter

and

Book
static hasMany = [chapters: Chapter]

I want to make request Book.findAll() and use it into gsp with nested objects, as book.chapters

I used option static [mapping = chapters lazy: false], but with application.logSql = true I see many queries select * from chapter where book_id = ?

Is any way to avoid multiple requests?

Answer 1

Using lazy: false means that GORM won't load automatically the hasMany records. Instead, you can try fetch: 'join' along with lazy: false.

https://grails.github.io/legacy-gorm-doc/6.0.x/hibernate/manual/#querying:~:text=You%20can%20also%20use%20fetch%3A%20%27join%27%20instead%20of%20lazy%3A%20false%20%2C%20in%20which%20case%20GORM%20will%20only%20execute%20a%20single%20query%20to%20get%20the%20airports%20and%20their%20flights

Another way is using fetch when using list: https://docs.grails.org/6.1.2/ref/Domain%20Classes/list.html

To be sincere, I never make it work properly. So, we managed to do that more manually using createAlias and projections:

Domain.createAlias().list() {
    projections {
        property "columnA"
        property "anotherDomain.column"
    }

    setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) // Make result return as Map

    createAlias("anotherDomain", "anotherDomain")
}

This won't be lazy, but since a Map is returned, you avoid dirty checking.