Unique Validation during update failing

@whoisthisstud what if you try it is unique excluding the current row:

'zip_code' => 'integer|unique:cities,id,'.$this->id

I guess you have id in your cities table.

@nakov, I am still learning, so please excuse my ignorance if I'm misunderstanding this, but wouldn't I need it to focus on the zip_code field to ensure that respective field is unique? If I change the second parameter of 'unique' to id, how does Laravel know that the zip_code field must be unique, on create?

@whoisthisstud sorry, my bad. You are right, the second one should be zip_code and I am curious have you debugged if there is a value in the $this->id when you are trying?

Or use request('id') or request('city') if you are using route model binding.

@nakov,

here is my controller's update method

public function update(StoreCityRequest $request, City $city)
{
    $validated = $request->validated();

    City::where('id', $city->id)->update($validated);

    return redirect()->route('view.city', [ $city->id ])->with('success', 'City updated');
}

In CityStoreRequest

public function rules()
{
    return [
        'name' => 'required|max:255',
        'zip_code' => 'integer|unique:cities,zip_code,'.request('id') // error's not unique
        'zip_code' => 'integer|unique:cities,zip_code,'.$this->id // error's not unique
        'zip_code' => 'integer|unique:cities,zip_code,'.$request->id // Doesn't work, "undefined variable: request"
    ];
}

@whoisthisstud what about $request->city or request('city') as I see you are using route model binding.

@nakov, if I use $request->city, I then get errors on the non-updating fields, such as "state_id" and the timestamps.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'state_id:1' in 'where clause' (SQL: select count(*) as aggregate from `cities` where `zip_code` = 12345 and `state_id:1` <> {"id":6 and `name:"Test City"` = zip_code:"12345" and `deleted_at:null` = created_at:"2019-12-26 15:12:45" and `updated_at:"2019-12-26 15:12:45"` = views:0})

@whoisthisstud okay, but now this means that the validation passes, and the problem is here:

    City::where('id', $city->id)->update($validated);

?

What does the dd($validated) returns?

@nakov nothing. same error. I'm dumping right after $validated = $request->validated(); and it gives the same error as without it.

@whoisthisstud but I don't get it why state_id now.. can you show your full rules() method now after the change?

Try this maybe:

'zip_code' => 'integer|unique:cities,zip_code,id'.request('city') 

so the except is = id.

@nakov, when I'm creating the city, I'm injecting the state_id into the create method, so I'm not validating it.

The table itself has the following columns:

id
state_id (unsigedBigInteger)
name (string)
zip_code (integer: unique)
views (integer, default: 0)
timestamps

This is the store method for City

public function store(State $state, StoreCityRequest $request)
{
    $validated = $request->validated();

    City::create($validated + ['state_id' => $state->id]);

    return redirect()->route('view.state', [ $state->id ])->with('success', 'City added');
}

This is the update method for City

public function update(StoreCityRequest $request, City $city)
{
    $validated = $request->validate();

    //dd($validated);

    $city = City::where('id', $city->id)->update($validated);

    return redirect()->route('view.city', [ $city->id ])->with('success', 'City updated');
}

Here are my current rules for StoreCityRequest.php

public function rules()
{
    return [
        'name' => 'required|max:255',
        'zip_code' => 'integer|unique:cities,zip_code,id'.request('city')
    ];
}

@whoisthisstud now it fails on update or on create?

You have a slight difference in the update method, you use validate instead of validated

    $validated = $request->validated();

@nakov that was just me testing and I just corrected. Didn't change that I still get SQL error mentioned above.

Creating works fine. Updating, with your recommendations, errors with the above-mentioned SQL error.

This error. Also, why is the query trying to count the records?

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'state_id:1' in 'where clause' (SQL: select count(*) as aggregate from `cities` where `zip_code` = 12346 and `state_id:1` <> id{"id":7 and `name:"Test City2"` = zip_code:"123457" and `deleted_at:null` = created_at:"2019-12-26 16:04:45" and `updated_at:"2019-12-26 16:04:45"` = views:0})

@whoisthisstud before returning the rules array dump the request('city') using dd(request('city')) see what it spits out, it seems like is an instance of the city model.

or use $this->city->id.

@nakov, 'zip_code' => 'integer|unique:cities,zip_code,id'.$this->city->id worked. thank you!

@whoisthisstud phew :D glad it worked out man. Happy coding! :)

So the error was as it was trying to compare the whole object as a JSON :) pretty weird, but we've got there.

@whoisthisstud I just thought that this might throw an exception now when creating a new one as $this->city will be null so calling id on null will fail.

Use $this->city->id ?? null or optional($this->city)->id instead.

Let me know if I guessed it right :)

@nakov, you're correct. $this->city->id ?? null did not work, but optional($this->city)->id did. Thank you again!

( $this->method() == 'POST' ? 'a rules' : 'other rules' )