Laravel Getting sql error when trying to give unique field validation

Question

This is from validation that I have used for tag form validation

public function rules()
{
     return [
         'name' => 'required|max:50|min:3|unique:tags,name,'.$this->tag,
     ];
}

My controller code

public function update(TagValidation $request, Tag $tag )
{
    $tag->update($request->all());
}

I am trying to avoid unique filed validation problem when trying to update. After use

unique:tags,name,'.$this->tag

I am getting below sql error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name:"abc"' in 'where clause' (SQL: select count(*) as aggregate from `tags` where `name` = abc and `name:"abc"` <> {"id":14 and `created_at:"2020-06-13T16:24:36`.`000000Z"` = updated_at:"2020-06-13T16:46:44.000000Z"})

But I have name column in database and store is working fine If I not use $this->tag in validation.

i would assume you want to pass the value of some field to the rule (perhaps the model's id), not the model instance itself when using `$this->tag`? — lagbox, Jun 13 '20 at 17:56
If I use $this->id , I am getting "The name has already been taken." — Niloy Rony, Jun 13 '20 at 17:58
`$this->tag` is a model instance ... you are concatenating a model instance to the string you are building (which serializes the model), not passing a single value like the id there ... `$this->tag->id` would be the id of the tag — lagbox, Jun 13 '20 at 18:00
After use $this->tag->id , I am getting validation error "The name has already been taken." After see this ans https://stackoverflow.com/questions/23587833/laravel-validation-unique-on-update I have used model instance. — Niloy Rony, Jun 13 '20 at 18:05

score 1 · Answer 1 · answered Jun 13 '20 at 17:58

take care because unique validation is

unique:table,column,except,idColumn

You are passing the value anme of tag but is not necessary

You actually use:

return [
   'name' => 'required|max:50|min:3|unique:tags,name,'.$this->tag,
];

but you need use this I show you working example for use the same validation on store and update (POST and PUT method):

public function rules()
{
    if ($this->method() == 'PUT') 
    {
        return [
             'name' => 'required|unique:tags,name,'.$this->id.',id',
        ];
    }elseif ($this->method() == 'POST'){
         return [
             'name' => 'required|unique:tags,name'
         ];
    }
}

Inclusive on Laravel 7* you can use Model directly

public function rules()
{
    // Check Create or Update
    if ($this->method() == 'PUT') 
    {
        return [
            'name' => 'required|unique:App\Tag,name,'.$this->id.',id'
        ];
    }elseif ($this->method() == 'POST'){
        return [
            'name' => 'required|unique:App\Tag,name'
        ];
    }
}

I have seen this ans where they given smaller solution https://stackoverflow.com/questions/23587833/laravel-validation-unique-on-update, but I am just getting sql error. — Niloy Rony, Jun 13 '20 at 18:07

score 1 · Accepted Answer · answered Jun 13 '20 at 18:14

You should be passing the id of the record you want to ignore the unique rule for which I would assume is that tag:

 'name' => 'required|max:50|min:3|unique:tags,name,'. $this->tag->id,

Or you can use the object version of the Rule which you can just pass the model directly to:

'name' => [
    'required', 'max:50', 'min:3', 
    Rule::unique('tags')->ignore($this->tag),
],

Laravel Getting sql error when trying to give unique field validation

2 Answers2