Custom Validation - Unique, 2 Columns

#1

Does anyone have any idea how to implement custom validation in Livewire? The documentation shows this example:

$validatedData = Validator::make(
            ['email' => $this->email],
            ['email' => 'required|email'],
            ['required' => 'The :attribute field is required'],
        )->validate();

I have a form where I am entering a town/city and a county. I need to validate that the combination entered is unique, i.e. not already in the database. Something like this, but I canā€™t figure out how to integrate into the Livewire validation which seems to be different from standard Laravel:

Rule::unique('locations')->where(function ($query) {
    return $query->where('name', $this->location->name)
        ->where('county_id', $this->location->county_id);
})

Any pointers would be appreciated.

#2

Hey, @ben-demotic

You must upgrade your application to the latest version to have the ability to do this

protected function rules()
{
return [
 'slug' => ['required', 'string', Rule::unique('locations')->where(function ($query) {
        //
    })],
];
}

For more info see this:

#3

Hi @skywalker,

Thanks for your reply.

I am on the latest version now (I was one minor release behind), but I still canā€™t get this to reliably work.

For instance, on my component I have:

public Location $location;

    protected $rules = [
        'location.name' => 'required|string|max:255',
        'location.county_id' => 'required|numeric',
    ];

To introduce the unique test I swap my $rules property for:

    protected function rules()
    {
        return [
            'location.name' => [
                'required',
                'string',
                'max:255',
                Rule::unique('locations')->where(function ($query) {
                    return $query->where('name', $this->locations->name)
                        ->where('county_id', $this->locations->county_id);
                    })
            ],
            'location.county_id' => 'required|numeric',
        ];
    }

This gives the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ā€˜location.nameā€™ in ā€˜where clauseā€™ (SQL: select count(*) as aggregate from locations where location.name = Example and (name = Example and county_id = 62))

Indeed the column in not found, as the table is called ā€œlocationsā€. The property ā€œlocation.nameā€ is on the model Location.

If I change all the references of $location to $locations, then my model property becomes locations.name, and it works as expected.

Actually, the validation SQL is wrong because it shouldnā€™t include the first where clause which contains the erroneous table name.

Am I doing something wrong or do you think this is a bug?

#4

Use

protected function rules()
{
  return [
    'location' => ['string', 'required', Rule::unique('table-name')->ignore($model->id)],
  ]
}
#5

Hi @skywalker,

Thanks again for your response.

However this canā€™t work either as ā€œlocationā€ is not a property on the component.

I think now that right from the start the syntax has been correct, and that it is definitely a bug in the SQL which is generated to test the validation rule.

SQLSTATE[42S22]: Column not found: 1054 Unknown column ā€˜location.nameā€™ in ā€˜where clauseā€™ (SQL: select count(*) as aggregate from locations where location . name = Example and ( name = Example and county_id = 62))

The first part of the where clause (location.name = Example) is incorrect, this is pulling in the name of the attribute under validation from the rules array, which is dependent on the name of the variable holding the instance of the Location model. So if I named the variable $socks then the validation array would contain socks.name and the where clause would be written socks.name = Example.

Iā€™ve just tried to implement this again on another component and the behaviour is the same.

I think I am going to open an issue on Github.

1 Like
#6

A possible answer. Specify the column name directly on the Rule as a second parameter to the unique method.

Rule::unique('locations', 'name')->where(function ($query) {
    return where('county_id', $this->locations->county_id);
})

This is mentioned in:

https://github.com/livewire/livewire/issues/1628

Which is linked to the issue you referenced. The workaround mentioned in #1628 is still required for unique rules to function properly.

1 Like