CODEWITHSUNDEEP
laravellaravel-5
cooka
cooka

Reputation: 33

Laravel avoid overlapping dates

I am new to Laravel and am trying to implement reservation dates that can not overlap on any day.

I have a model called 'Bookings' that includes the room_id, start_date and end_date.

I have validation that checks that the end date can not be before the start:

$this->validate($request, [
        'start_date' => 'required|date',
        'end_date' => 'required|date|after_or_equal:start_date',
]);

However I am not sure how to check that the date range does not conflict with any other date ranges of the same room_id stored in the bookings table (as one room can not be booked twice in the same range).

Any help would be appreciated,

Thanks!

Upvotes: 3

Views: 4845

Answers (5)

Algen Benedict Neverio
Algen Benedict Neverio

Reputation: 1

if(DB::table('rooms')->whereBetween('start_date', [$request->start_date, $request->end_date])
    ->orwhereBetween('end_date',[$request->start_date, $request->end_date])->exists())
{
  echo "the room is busy in the interval you selected";
}

Upvotes: 0

Sahil Jain
Sahil Jain

Reputation: 471

I had a similar usage where i needed to check if person has already scheduled something in date range

An example laravel code below :

 $start = Carbon::parse($request['start_date'])->format('Y-m-d 00:00:00');
 $end = Carbon::parse($request['end_date'])->format('Y-m-d 23:59:59');
 $existsActive = Microsite::where(function ($query) use ($start) {
                                      $query->where('start_date', '<=', $start);
                                      $query->where('end_date', '>=', $start);
                                  })->orWhere(function ($query) use ($end) {
                                      $query->where('start_date', '<=', $end);
                                      $query->where('end_date', '>=', $end);
                                  })->count();
 if($existsActive > 0 ){
    echo "Already exists";  
 }

Upvotes: 4

Juan Carlos Estrada Nieto
Juan Carlos Estrada Nieto

Reputation: 101

In a typical overlaping, given two events A and B, you have to consider four scenarios:

  1. Event A contains event B
  2. Event B contains event A
  3. End date of event A overlaps with start date of event B
  4. End date of event B overlaps with start date of event A

So, in sql to check if a room is busy in a interval of dates, lets say 2018-11-20' and '2018-11-30' it would be:

select * 
from rooms 
where 
    start_date between '2018-11-20' and '2018-11-30' or 
    end_date between '2018-11-20' and '2018-11-30' or 
    '2018-11-20' between start_date and end_date or 
    '2018-11-30' between start_date and end_date;

In laravel what I do is to create a scope in the model.

public function scopeByBusy($query,$start_date,$end_date) 
{ 
    return $query->whereBetween('start_date', [$start_date, $end_date]) 
        ->orWhereBetween('end_date', [$start_date, $end_date]) 
        ->orWhereRaw('? BETWEEN start_date and end_date', [$start_date]) 
        ->orWhereRaw('? BETWEEN start_date and end_date', [$end_date]); 
}

Finally, I can verify if the room with the id 95 is busy between '2018-11-20' and '2018-11-30' like this:

$room_id = 95;
$start = '2018-11-20';
$end = '2018-11-30';
$exists = Rooms::where('id', $room_id)
    ->byBusy($start, $end)
    ->first();

if($exists)
{
    echo "the room is busy in the interval you selected";
}

I hope it helps you.

Upvotes: 8

Bhagyashree Sarkar
Bhagyashree Sarkar

Reputation: 519

You have to check that in database. So you can do this: 

$result = Bookings::where('start_date', '<=' $request->start_date)->where('end_date', '>=' $request->end_date)->where('room_id',$request->room_id)->first();
if(!$result){
  // Code to Book room
}

Upvotes: -2

Faiez
Faiez

Reputation: 305

There's built-in Carbon method isPast() so you can use:

$start_date->isPast()
$end_date->isPast()

You can check If both selected were booked in past.

If you haven't used Carbon this is the link: https://carbon.nesbot.com/docs/

Upvotes: 0

Related Questions