Writing code for reservations(API)

Question

I have done most of the app I'm working on and now I think I'm stuck in one change. I have the ideas of doing it but the problem I really couldn't implementing it. I hope I can find some help here.

I have this complicated code. It takes two dates and check car_reservation pivot tables for overlapping.

 $table->integer('car_id')->unsigned();
 $table->foreign('car_id')->references('id')->on('cars');

 $table->integer('reservation_id')->unsigned();
 $table->foreign('reservation_id')->references('id') >on('reservations');

The relationship is in Reservation model :

 public function cars()
{
    return $this->belongsTo('App\Models\Access\Car','car_reservation');
}

Here is the code that I'm trying to debug and make it work:

public function get(Request $request)
{

   $appointments = Reservation::with('cars')->get();

   foreach ($appointments as $appointment) {

     $from = Carbon::parse($request->from);
     $to = Carbon::parse($request->to);
     $eventStart = Carbon::instance(new DateTime($appointment['dt_start']));
     $eventEnd = Carbon::instance(new DateTime($appointment['dt_end']))->subSecond(1);

     // A spot is taken if either the from or to date is between eventStart and eventEnd
     // or if the evenStart and eventEnd are between the from and to date.

      if ($from->between($eventStart, $eventEnd) || 
       $to->between($eventStart, $eventEnd) ||
      ($eventStart->between($from, $to) &&
       $eventEnd->between($from, $to))) {
            return response()->json('false');// test response 
      }
            return response()->json('no appointments overlapping');
      }
}

But What I need help with is writing these steps, and I think it would work perfectly.

(1) A method to get the appoitmenets from car_reservation within an optional date range. Ex: getAppointments($from=null,$to=null

(2) method to loop all the cars and arrange them in array. Ex: getCars

(3) A method to check availability. Ex: isSlotAvailable($from,$to,$appoitments);

(4) A method that does the work:

function getAvailability(Request $request)
{
    $slots = [];

    $from = $request->input('from');

    $to = $request->input('to');

    foreach ($this->getcars() as $cars) {

        $appointments = $this->getAppointments($cars, $from, $to);

        $slot[$cars] = $this->isSlotAvailable($from, $to, $appointments);
    }

    return $slots;
}

Then at the end I hope to get something like ['Car' => true, 'Car' => false]

You help will be really appreciated. I've been coming up with many codes but they all look like my original one.

UPDATE

public static function findAppointmentsBetweenDates($start, $end)
{
    $appointments = Reservation::wherenotBetween('from_date',array($start,$end))->get();

    return $appointments;
}

then in my controller

public function get(Request $request)
{
    $results = array();

    $car = Car::doesntHave('reservations')->get();


    if (!$car->isEmpty()) {
        $results[] = $car;

        return Response::json(['array'],$results);
    }


$from = Carbon::parse($request->from)->addHour(2);
    $to = Carbon::parse($request->to);

    $appointmentsBetweenDates = Reservation::findAppointmentsBetweenDates($from, $to);

    foreach ($appointmentsBetweenDates as $appointment)
    {
        $results = ($appointment->cars);


    }
    return Response::json(['array',$results]);
    }

Answer 1

We can leverage the power of the database to filter available cars in the time slot. Using query builder we can look for every car that does not have a reservation during the specified window.

In Car

/**
 * @param $from Carbon
 * @param $to Carbon
 * @return \Illuminate\Database\Eloquent\Collection
 */
public static function whereAvailableBetween($from, $to) {
    $rows = DB::table('car')
        ->leftJoin('car_reservation', 'car_reservation.car_id', '=', 'car.id')
        ->leftJoin('reservation', function($join) use ($from, $to) {
            return $join->on('reservation.id', '=', 'car_reservation.reservation.id')
                ->where('reservation.date_start', '>=', $from->toDateTimeString())
                ->where('reservation.date_end', '<=', $to->toDateTimeString());
        })
        ->whereNull('reservation.id')
        ->get();

    return $rows->map(function($r, $k) {
        return new static($r);
    });
}

Now, we can use \Car::whereAvailableBetween($date_start, $date_end). To return it as JSON from your controller, you could:

public function get(Request $request)
{
    return Car::whereAvailableBetween(Carbon::parse($request->from), Carbon::parse($request->to));
}

EDIT

I missed that the desired end format is

['Car' => true, 'Car' => false]

So you could modify the above method and return a standard collection with these details by removing the whereNull clause:

/**
 * @param $from Carbon
 * @param $to Carbon
 * @return \Illuminate\Support\Collection
 */
public static function whereAvailableBetween($from, $to) {
    $rows = DB::table('car')
        ->leftJoin('car_reservation', 'car_reservation.car_id', '=', 'car.id')
        ->leftJoin('reservation', function($join) use ($from, $to) {
            return $join->on('reservation.id', '=', 'car_reservation.reservation.id')
                ->where('reservation.date_start', '>=', $from->toDateTimeString())
                ->where('reservation.date_end', '<=', $to->toDateTimeString());
        })
        ->select('car.*', 'reservation.id AS reservation_id')
        //->whereNull('reservation.id') if it is null then no reservation, else there is a reservation
        ->get();

    return $rows->map(function($row, $k) {
        $asArray = (array)$row;
        $reservation_id = array_pop($asArray);
        $available = (is_null($reservation_id)) ? true: false;

        return [
            'car' => new static($asArray),
            'available' => $available
        ];
    });
}

Keep in mind that you can also add an attribute to a model. So we could do $car->available = true