Reputation: 1757
Here DealerID can be nullable and CarID, TyreID are unqiue.
The problem I have noticed is: Grails ignores nulls in unique constraints.
Upvotes: 1
Views: 3415
Reputation: 24393
I don't think the question is clear, but this may be a bug in Grails.
Upvotes: 3
Reputation: 1
This is an old topic, but I thought I'd share my trusty solution anyway. It utilizes an extra column, so it needs a little bit extra storage but keeps performance just as fast as if there was no nullable involved. Matching your example, you add / change (assuming your IDs are simple Strings due to the blank constraint and for simplicity):
String dealerID
String dealerIDConstraint
String carID
String tyreID
static constraints = {
dealerID nullable: true
dealerIDConstraint blank: false
carID blank: false, unique: ['tyreID','dealerIDConstraint']
tyreID blank: false
}
// This will cover updates from web pages.
def beforeValidate() {
updateDealerIDConstraint()
}
// This will cover direct access.
void setDealerID(String dealerId) {
this.dealerId = dealerId
updateDealerIDConstraint()
}
/**
* Ensure the validation only field is up to date.
*/
private void updateDealerIDConstraint() {
dealerIDConstraint = dealerID ?: tyreID + "-" + carID
}
The constraint field will always have a value and will not get in your way when it is supposed to be irrelevant for the uniqueness.
Upvotes: 0
Reputation: 21
static constraints = {
dealerID nullable:true;
carID unique:'dealerID', blank:false;
tyreID unique:'carID';
}
This will not work as intended. This will make carID unique only within the scope of dealerID. tyreID will be unique within the scope of 'carID'. What this means is that validation would fail when entering this perfectly valid row:
CarID TyreID DealerID 01 02 NULL Existing row 01 02 95 New row is rejected
This is because TyreID is not unique - it conflicts with an existing CarID. We want the unique constraint to apply to the scope of all specified fields
As far as I know, this problem still exists with the latest version of Grails (1.3.7)
In your domain class, write a custom validator for the field that looks for an existing record using CriteriaBuilder:
class MyClass { ... static constraints = { ... carID( validator: { Integer carID, MyClass thisInstance -> def existingRecord = MyClass.withCriteria(uniqueResult:true){ ne('id', thisInstance.id) if (thisInstance.tyreID) { eq('tyreID', thisInstance.tyreID) } else { isNull('tyreID') } if (thisInstance.dealerID) { eq('dealerID', thisInstance.dealerID) } else { isNull('dealerID') } }) if (existingRecord) { return ['myClass.duplicate', existingRecord.id] } else { return true } } )
and in your message properties, you can alert the user that this record conflicts with a specific record:
message.properties ... myClass.duplicate = {0} is identical to existing record {3}
It's a hacky solution, requires a potentially unnecessary database hit, and looks ugly. I don't like it, but it works.
Upvotes: 2
Reputation: 896
I know this is way late in the game, but to make the combinations unique, I'd present the constraints as such:
static constraints = {
dealerID nullable:true;
carID unique:'dealerID', blank:false;
tyreID unique:'carID';
}
This should ensure that even if Grails ignores the unique constraint on carID due to dealerID being null, you won't get a combination of carID and tyreID that clashes. At least that's how I think it works.
Upvotes: 0