Filament•13mo ago

Unique() validation with multiple columns

I have a polymorphic relationship between e.g. a Client and Addresses. I want to be able to add multiple addresses for a Client. There is however a unique constraint on the table that looks like this:

$table->unique(['address_type_id', 'addressable_id', 'addressable_type'], 'unique_addressable');

$table->unique(['address_type_id', 'addressable_id', 'addressable_type'], 'unique_addressable');

This is doing exactly what it should do, but it causes some troubles in the Filament Panel. If I add an address the first time, it is no problem. If I want to add a new address with the same address_type_id I get this error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

Which is also something I would expect. Only it returns in a 500 error. What I want is to get a nice validation error somewhere on my form, presumably with the AddressType Select field. I tried to use this rule:

->unique(ignoreRecord: true)

->unique(ignoreRecord: true)

Then I get a nice validation message below my Select field, but it also triggers when I just update an existing record. Next, I tried this rule:

->unique(ignoreRecord: true, modifyRuleUsing: function (Unique $rule, $state) {
    return $rule
        ->where('addressable_type', Address::class)
        ->where('address_type_id', $state);
})

->unique(ignoreRecord: true, modifyRuleUsing: function (Unique $rule, $state) {
    return $rule
        ->where('addressable_type', Address::class)
        ->where('address_type_id', $state);
})

This allows me to update and save my existing record, but still when trying to add a new record with the same Address Type, it gives me this error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

So, I want to know how I can the right validation message inside my form, instead of a 500 error. Not sure if this could work because with new records you only know when inserting them into the database if the fields are unique. How would this work in Filament?

3 Replies

Remi Hindriks•13mo ago

Same question here!

Daniel PlompOP•12mo ago

Anyone an idea?

Remi Hindriks•12mo ago

I solved it, sort of, i added a custom rule to the last required field (only seemed to work for required fields) In my case the combination of 'name', 'starts_at' and 'ends_at' for a lessongroup should be unique. So on the ends_at DatePicker field i added the following rule Best way i found to solve it 🤷‍♂️

->rules([
    function (Closure $get) {
         return function (string $attribute, $value, Closure $fail) use ($get) {
             if (LessonGroup::where('lesson_location_id', current_location()->id)
                              ->where('name', $get('group_name'))
                              ->where('starts_at', Carbon::parse($get('starts_at'))->format('Y-m-d'))
                              ->where('ends_at', Carbon::parse($get('ends_at'))->format('Y-m-d'))->exists()) {
                                  $fail(__('The combination of groupname, start date and end date already exists'));
                              }
           };
      },
])

->rules([
    function (Closure $get) {
         return function (string $attribute, $value, Closure $fail) use ($get) {
             if (LessonGroup::where('lesson_location_id', current_location()->id)
                              ->where('name', $get('group_name'))
                              ->where('starts_at', Carbon::parse($get('starts_at'))->format('Y-m-d'))
                              ->where('ends_at', Carbon::parse($get('ends_at'))->format('Y-m-d'))->exists()) {
                                  $fail(__('The combination of groupname, start date and end date already exists'));
                              }
           };
      },
])

Gaming

Programming

Unique() validation with multiple columns