Grails unique constraint optimization part 2

In the previous blog Grails performance optimization – Unique constraint we have optimized Grails unique constraint. Now in this blog we will check another use case and will optimize unique constraint according to it. Suppose we have two domains having the following structure:

[code]
class Address {
String addressLine1
String addressLine2

static constraints = {
}
}
[/code]

[code]
class User {

String firstName
String lastName
String emailId
Address address

static constraints = {
address nullable: true, blank: false
emailId nullable: false, blank: true, email: true, unique: true
}

static mapping = {
address fetch: ‘join’
}
}
[/code]

Now create user object and check the database queries triggered by grails application:

[code]
User user = new User(firstName: ‘Deepak’, lastName: ‘Mittal’, emailId: ‘deepak.krmittal@intelligrape.com’)
if(user.validate() && user.save(flush: true, validate: false)) {
println(‘Success’)
} else {
println(‘Error’)
}
[/code]

Check the application console, it has the queries like this:

[code]
Hibernate: select this_.id as id1_1_1_, this_.version as version2_1_1_, this_.address_id as address_3_1_1_, this_.email_id as email_id4_1_1_, this_.first_name as first_na5_1_1_, this_.last_name as last_nam6_1_1_, address2_.id as id1_0_0_, address2_.version as version2_0_0_, address2_.address_line1 as address_3_0_0_, address2_.address_line2 as address_4_0_0_ from user this_ left outer join address address2_ on this_.address_id=address2_.id where this_.email_id=?
Hibernate: insert into user (version, address_id, email_id, first_name, last_name) values (?, ?, ?, ?, ?)
[/code]

In the first query for unique check, there are 2 issues.

1. User domain is joined with address domain due to fetch join specified in user mapping closure, which really doesn’t needed in unique check.
2. All fields of user and address domain are fetched while there is no need of them.

To solve the issue, we have replaced the unique constraint with custom validator like this:

[code]
class User {

String firstName
String lastName
String emailId
Address address

static constraints = {
address nullable: true, blank: false
emailId nullable: false, blank: true, email: true, validator: { val, obj ->
if (obj.id) {
if (User.countByEmailIdAndIdNotEqual(val, obj.id)) {
return "already.exist"
}
} else {
if (User.countByEmailId(val)) {
return "already.exist"
}
}
}
}

static mapping = {
address fetch: ‘join’
}
}
[/code]

Now again create the user object and check your application console, it has the queries as shown below:

[code]
Hibernate: select count(*) as y0_ from user this_ where this_.email_id=?
Hibernate: insert into user (version, address_id, email_id, first_name, last_name) values (?, ?, ?, ?, ?)
[/code]

Now your purpose is solved with a little bit optimized code.

Code written with Grails 2.4.3.