CODEWITHSUNDEEP
grailsgrails-orm
WaZ
WaZ

Reputation: 1757

Grails- Unique and Null

alt text

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

Answers (4)

Armand
Armand

Reputation: 24393

I don't think the question is clear, but this may be a bug in Grails.

Upvotes: 3

Planet
Planet

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

jskrivsethsbs
jskrivsethsbs

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)

This is my workaround

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

tzrlk
tzrlk

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

Related Questions