Ad

Validation For Three Unique Fields And Soft Deletes

Last year I made a laravel site with an events table where I needed three fields to be unique for any event (place, date and time). I wasn't able to set up a validation request to do this so I added an unique index for these three fields directly through phpmyadmin and catching the exception that could happen if a duplicated event was inserted.

So basically my store() method has a try/catch like this:

try {

    $event = new Event;
    $event->place = $request->input('place');
    $event->date = $request->input('date');
    $event->time = $request->input('time');
    $event->save();

    return view(...);
} catch (\Illuminate\Database\QueryException $e) {

    // Exception if place-date-time is duplicated
    if($e->getCode() === '23000') {
        return view('event.create')
                ->withErrors("Selected date and time is not available");
    }
}

Well, now I had to change the app so events could be soft deleted and I simply added the 'deleted_at' field to the unique index, thinking it would be so easy... This approach doesn't work anymore so I've been reading here and there about this problem and the only thing I get is I should do it through a validation request with unique, but honestly I just don't get the syntax for this validation rule with three fields that can't be equal while a fourth one, deleted_at, being null.

My app checks for the available places, dates and times and doesn't let the user choose any not available event but no matter how many times I've told them there's always someone who uses the browser back button and saves the event again :(

Any help will be much appreciated. Thank you!

Ad

Answer

This is not a good approach to solve the problem. You can do follow things to solve this problem

  • Before insert into database get a specific row if exist from database and store into a variable.

  • Then check the data is already stored into the database or not.

  • If data is already there create custom validation message using Message Bag Like below.
$ifExist = $event
            ->wherePlace(request->input('place'))
            ->whereDate(request->input('date'))
            ->whereTime(request->input('time'))
            ->exist();

if ($ifExist) return 'already exist';

It might help you.

Ad
source: stackoverflow.com
Ad