CODEWITHSUNDEEP
hibernategrailsgrails-orm
Dónal
Dónal

Reputation: 187339

Grails N+1 query

My Grails app uses the Spring Security and has the usual User, UserRole, and Role classes. The way these classes are modelled is little unusual, in that there are no hasMany mappings in either User or Role. Instead the classes are referenced solely through UserRole.

class UserRole implements Serializable {
  User user
  Role role
}

My understanding is that the relationship has been modelled like this for performance reasons, specifically, to reduce the possibility of N+1 queries.

In one part of my application I need to load all users and their roles. Conscious of the aforementioned problem, I tried to do it like this:

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

However when I try to access the User objects

usersByRole.each { it.user }

A separate query is issued to retrieve the data from the User table, so I've run into exactly the problem I was trying to avoid. I also tried the following, but it suffers from the same problem.

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

I should confess that I'm not altogether clear on the the difference between FetchMode.JOIN and FetchMode.SELECT, so if anyone could set me straight on that, it'd be appreciated.

Upvotes: 4

Views: 538

Answers (1)

Burt Beckwith
Burt Beckwith

Reputation: 75671

I tried several combinations but had the same results - if you look at the generated SQL there's no join in the original query, so it has to do extra queries to load the users and roles.

Others have had issues with this domain class - it looks like there's a bug in GORM with a composite key made up of domain classes, or something like that. Usually people are satisfied with an HQL workaround, and with any luck so will you :)

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

Upvotes: 6

Related Questions