laravel - Adapt my code by adding start_time and stop_time in my booking

Question

I have to add in my TrainingController two variables => start_time & stop_time.

In my old code I had this:

 $conflictTraining = Training::where('fk_motorbike', $request->get('fk_motorbike')) 
 ->whereDate('date_seance', "=" , Carbon::parse($date_seance)) 
 ->where('hour_start', "<=" , $request->get('hour_start')) 
 ->where('hour_end', ">=" , $request->get('hour_end'))
 ->where('fk_former', $request->get('fk_former'))
 ->first();

My problem is that I would like to do a checking. How Can I avoid a duplicate for my request $conflictTraining with start_time & stop_time..

Here is my code for now:

public function store(Request $request)
    {
        $request->validate([
            'date_seance' => 'required',
            'hour_start' => 'required',
            'hour_end' => 'required',
            'fk_motorbike' => 'required',
            'fk_former' => 'required',
            'fk_student' => 'required',
            'fk_typeseance' => 'required'


        ]);


        $start_time = Carbon::createFromFormat('d-m-Y H:s', $date_seance . ' ' . $hour_start);
        $stop_time = Carbon::createFromFormat('d-m-Y H:s', $date_seance . ' ' . $hour_end);


        $conflictTraining = Training::where('fk_motorbike', $request->fk_motorbike)
            ->where('start_time', "<=", $start_time)
            ->where('stop_time', ">=", $stop_time)
            ->first();


        if (isset($conflictTraining)) {
            return redirect()->route('trainings.index')
                ->with('error', 'training duplicate');
        }


        $data = $request->all();
        $data['start_time'] = $start_time;
        $data['stop_time'] = $stop_time;
        Training::create($data);
        return redirect()->route('trainings.index')
            ->with('success', 'Add');


    }

I thank you in advance for your help.

Answer 1

If you are asking how to add a new constraint for a couple of new database fields, start_time and stop_time, I think you are pretty close with your current query for $conflictTraining.

You didn't state what the specific problem was, but I'm guessing that these lines:

$start_time = Carbon::createFromFormat('d-m-Y H:s', $date_seance . ' ' . $hour_start);
$stop_time = Carbon::createFromFormat('d-m-Y H:s', $date_seance . ' ' . $hour_end);

are producing something that doesn't match the format in the database for start_time and stop_time. If you break down what you have created in those variables, you are basically telling Carbon to make a variable that looks something like this: "15-10-2019 10:00:00" (with the caveat that the 00 is seconds, not minutes since you used s instead of i).

So your first question should be, 'is my database storing the start time in the exact format that shows when I dump $start_time?' If not, the query will never produce a hit. You can certainly store your start times and stop times in the database as a a date and time... but keep in mind, you will be constrained by the date as well as the time if you do so. In other words, if you just want a start time to be 10:00 on any day, that won't work because the database is storing 10:00 on a specific date.

One way to resolve this is to keep this as simple as possible. If those times are NOT linked to a specific date, and are always going to be on the hour - save them as a simple number. E.g. 08 or 22. Then, you don't need to use Carbon, you are just comparing simple integers. If you want to use an actual time (you might want 18:20 or something), this is not much more complex - you just need to change the way you store and create the time.

You can play with this, but the general idea would be to just store / create the time in a consistent format between database and what you create from the form. So for start_time, you could perhaps save the hours and minutes in a different column. Or calculate the number of minutes after midnight and store as an integer. Or even store as text and use Carbon on both sides to make into a formatted object.

Personally I find it easiest to work with integers, so I would make a calc (maybe mins past midnight) and use that calc across the program to just stay with integers. There are a lot of ways to solve this - hopefully this explains the issue to you.