Check Available Timeslot Upon Date Selected

Question

Check Available Timeslot Upon Date Selected

I am currently building a scheduling platform with Livewire.

What Is Existing? Customers get an SMS and Email of their pickup IDs to come pick up their package once available from my client. However, this causes too many crowd at my client's office since most, if not all of them just show up almost, if not at the same time. So my client wants me to build a scheduling app whereby from the SMS and Email alerts, the customers are asked to book a schedule before they can come at all.

What I Did Initially?

• Created two databases: Schedules and Timeslots --> Schedules contain pickup_id, location, scheduled_at, timeslot_id --> Timeslots contain name, limit
• When a customer selects date and timeslot, a query runs at the backend to count the number of schedules for that particular timeslot against the limit of that timeslot

$schedules  = Schedule::where([
                ['scheduled_at', $this->scheduled_at],
                ['timeslot_id', $this->timeslot_id]
            ])->get();
$timeslot   = Timeslot::where('id', $this->timeslot_id)->first();

if (count($schedules) >= $timeslot->limit) {
    session()->flash('callout', ['type' => 'error', 'message' => 'Pickup Date is full for that time slot! Kindly choose another date or slot!']);
}

• If the count of schedules is less than timeslot limit, proceed. Else, return an alert that the timeslot is full, select another date or timeslot.

To this stage, easy-peasy.

However, what my client want is that the select dropdown for timeslots should display only the available timeslot as soon as a customer selects the date. What I am thinking to do is:

• Select date
• Background query gets all the schedules for that day
• Get all the timeslots for all the schedules for that day
• For each timeslot, query the count of schedules for that timeslot against the limit of that timeslot.
• If the schedule count is less than limit, display the timeslot. Else, do not display the timeslot

That is the idea I have in my head, however, I do not know how to represent it as code. Is the idea even the right one? Do I need to create a pivot table for this or is there a way I can write the builder/eloquent to do this without creating any pivot table? I need your help here.

Schedule blade looks like this:

<div class="col-md-6 mb-3" wire:ignore.self>
    <label class="form-label-lg required">Pickup Date</label>

    <div class="input-group">
        <input type="text" id="datepicker01" class="datepicker form-control form-control-lg @error('selectedScheduledAt') is-invalid @enderror" placeholder="yyyy/mm/dd" autocomplete="off" data-provide="datepicker" data-date-autoclose="true" data-date-format="yyyy/mm/dd" data-date-today-highlight="true" data-date-start-date="new Date();" data-date-week-start="1" data-date-days-of-week-disabled="0,6" onchange="this.dispatchEvent(new InputEvent('input'))" wire:model="selectedScheduledAt" required>

        <label for="datepicker01" class="input-group-text pointer text-white bg-touch"><i class="bi-calendar3"></i></label>
    </div>

    @error('selectedScheduledAt') <span class="error">{{ $message }}</span> @enderror
</div>

<div class="col-md-6 mb-3">
    <label class="form-label-lg required">Time Slot <em class="font-sm">(Choose Pickup Date)</em></label>
    <select class="form-select form-select-lg @error('slot_id') is-invalid @enderror" wire:model.defer="slot_id" required>
        <option value="">{{ __('Select Time Slot') }}</option>

        @if (!empty($timeslots))
            @foreach($timeslots as $timeslot)
                <option value="{{ $timeslot->id }}">{{ $timeslot->name }}</option>
            @endforeach
        @endif
    </select>

    @error('timeslot_id') <span class="error">{{ $message }}</span> @enderror
</div>

And my Livewire component looks like this:

public $selectedScheduledAt = NULL;
public $timeslots = NULL;

public function updatedSelectedScheduledAt($scheduled_at)
{
    // this is where I am to get the available $this->timeslots to display but I am hooked.
}

Answer 1

In your Timeslot Model, add this code below

public function schedules() {
    return $this->hasMany(Schedule::class, 'timeslot_id', 'id');
}

In your Livewire component, add this code below

public $timeslots = [];

public function updatedSelectedScheduledAt($scheduled_at) {
  $timeslots = Timeslot::with('schedules')->get();

  foreach ($timeslots as $key => $timeslot) {
    $allocated = collect($timeslot->schedules)->where('scheduled_at', $scheduled_at)->where('timeslot_id', $timeslot->id)->count();
    if ($allocated < $timeslot->limit) {
        $this->timeslots[] = $timeslot;
    }
  }
}