grails

Grails Avoid N+1 in hasMany case


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?


Solution

  • 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.