Changing time to remove duplicates

I've decided to add some data integrity to my appointments table and allow only one customer per appointment date and time. The problem is that prior to this, I did allow more than one customer per time slot.

I would like to change the database so it's unique on the appt_date, appt_time combo.

Appointments used to be every 10 minutes, and sometimes more than one person would be in that time slot (2 people), but I finally realized it would be simpler to just make it one person every five and lessen the logic on the front end.

I need to update my db first, to get rid of all the duplicates. The time being off by a few minutes will not matter. Here is what I had so far but it isn't updating on my test end:

Artisan::command('singleAppts', function(){
    $start = Carbon::create(2023,01,1,0,0,0);
    while($start <= Carbon::now()) {
        print " Date: ".$start."\n";
        foreach ([1,2] as $provider) {
            print "  Doc: ".$provider;
                $appts = Appt::select(['id','appt_time','appt_date','provider_id'])->whereProviderId($provider)->where('appt_date',$start)->orderBy('appt_time')->get();
            $dupes = $appts->groupBy('appt_time');
//            dd($dupes);
            if($dupes->count() > 1) {
                foreach($dupes as $index=> $time) {
//                    print $index;
                    if($time->count() > 1) {
                        foreach($time as $key => $appt) {
                                $t = Carbon::createFromTimeString($appt->appt_time);
                                $dupes[$index][$key]['appt_time'] = $t->addMinutes($key*5);
                        }
                    }

//                    print "Appt: " . $appt->appt_time;
                }
            } else {
                print "     no dupes found.\n";
            }
        }
        $start->addDay();
    }

});

It looks like it SHOULD update the db, but I'm missing something and it doesn't. Please help me understand what I'm doing wrong.

LaryAI

2 years ago

Level 58

It seems like you're on the right track with your script, but you're missing the actual database update operation. You're modifying the $dupes array with the new times, but you're not persisting those changes back to the database.

Here's how you can update your code to save the changes to the database:

Artisan::command('singleAppts', function () {
    $start = Carbon::create(2023, 01, 1, 0, 0, 0);
    while ($start <= Carbon::now()) {
        print " Date: " . $start . "\n";
        foreach ([1, 2] as $provider) {
            print "  Doc: " . $provider;
            $appts = Appt::select(['id', 'appt_time', 'appt_date', 'provider_id'])
                ->whereProviderId($provider)
                ->where('appt_date', $start)
                ->orderBy('appt_time')
                ->get();
            $dupes = $appts->groupBy('appt_time');

            foreach ($dupes as $timeGroup) {
                if ($timeGroup->count() > 1) {
                    // Skip the first appointment as it doesn't need to be moved
                    foreach ($timeGroup->slice(1) as $index => $appt) {
                        $t = Carbon::createFromTimeString($appt->appt_time);
                        // Update the appointment time by adding 5 minutes for each subsequent appointment
                        $newTime = $t->addMinutes(($index + 1) * 5)->format('H:i:s');

                        // Update the appointment in the database
                        Appt::where('id', $appt->id)->update(['appt_time' => $newTime]);
                    }
                } else {
                    print "     no dupes found.\n";
                }
            }
        }
        $start->addDay();
    }
});

Here's what I changed:

I used slice(1) to skip the first appointment in each time group since we don't need to change the time for the first appointment.
I used format('H:i:s') to format the new time correctly for the database update.
I added the Appt::where('id', $appt->id)->update(['appt_time' => $newTime]); line to actually update the appointment in the database.

Make sure to test this script on a backup of your database first to ensure it works as expected and doesn't cause any data loss. Also, consider adding transactional logic to ensure that all changes are applied correctly or rolled back in case of an error.

1 like

Please or to participate in this conversation.