Request Grails N + 1

Question

My Grails uses Spring Security and has the usual User , UserRole and Role classes. The way to model these classes is a bit unusual since there are no hasMany mappings in User or Role . Instead, classes refer exclusively to UserRole .

 class UserRole implements Serializable { User user Role role } 

My understanding is that relationships were modeled this way for performance reasons, in particular to reduce the likelihood of N + 1 queries.

In one part of my application, I need to download all users and their roles. Aware of the above problem, I tried to do it as follows:

 def usersByRole = UserRole.createCriteria().list { fetchMode("user", FetchMode.JOIN) fetchMode("role", FetchMode.JOIN) } 

However, when I try to access User objects

 usersByRole.each { it.user } 

A separate request is issued to retrieve data from the User table, so I ran into a problem that I was trying to avoid. I also tried the following, but it is experiencing the same problem.

 def usersByRole = UserRole.createCriteria().list { fetchMode("user", FetchMode.SELECT) fetchMode("role", FetchMode.SELECT) } 

I must admit that I do not quite understand the difference between FetchMode.JOIN and FetchMode.SELECT , so if someone could set me directly to this, it would be helpful.

Answer 1

I tried several combinations, but had the same results. If you look at the generated SQL, there is no connection in the original query, so it must make additional queries to load users and roles.

Others had problems with this domain class - it looks like GORM has an error with a compound key consisting of domain classes, or something like that. Usually people are happy with the HQL workaround, and with any luck, you too :)

 def usersByRole = UserRole.executeQuery( 'select ur from UserRole ur ' + 'left join fetch ur.user ' + 'left join fetch ur.role')