How to optimize this GORM query

问题:

I have a query which makes 2 database queries. I am new to GORM and don't know how to optimize it into 1 query using criteria, or a where clause or something else. I'm using springsecurity as well and have a User class for login security. I have a Profile class with a User object:

class Profile {
   User user
}

In my controller the session stores the user id. When I want to load the profile to update it my code is currently:

def user = User.get(springSecurityService.principal.id) // 1st db query
def domainObject = Profile.findByUser(user) // 2nd db query

I've tried a few query variations through trial and error but can't seem to find one that doesn't generate an error. This seems like it would be trivial. The pseudo-query would be 'get me the profile for the profile.user that has this user id'.

回答1:

You can use the load method to avoid the first database query:

def user = User.load(springSecurityService.principal.id)
def domainObject = Profile.findByUser(user)

This works because load doesn't trigger a database lookup until you access any property other than the id, but that's all that's needed for the findByUser since it's the foreign key.

回答2:

I would do:

def profile = Profile.createCriteria().list {
    user{
        eq 'id',springSecurityService.principal.id
    }
}[0]

Or

def profile = Profile.executeQuery('select p from Profile p where p.user.id = :userId',[userId:springSecurityService.principal.id])[0]