CODEWITHSUNDEEP
phpmysqllaraveltimeeloquent
Daniel White
Daniel White

Reputation: 3377

Laravel Eloquent Query to Check Overlapping Start and End DateTime Fields

I am trying to create a booking system where a user selects a start date and time. The system creates an end date and time based on the longest product service time. This is currently all figured out.

I have a table in my database for my events that has:

vendor_id | user_id | start | end

The start and end columns are of type DateTime.

In my controller i'm trying to find out if a particular date and time is available, but for some reason I can't get it to show me items where the times overlap.

Here is my controller code:

$items = $query->where('vendor_id', $user->vendor->id)->where('start', '<=', date('Y-m-d H:i:s', strtotime($start)))->whereDate('end', '>=', date('Y-m-d H:i:s', strtotime($end)))->get();

So a few example scenarios...

I have an event in the system that has a start of 2019-01-01 13:00:00 and an end time of 2019-01-01 17:00:00.

If I choose a start time of 2019-01-01 12:00:00 and an end time of 2019-01-01 16:00:00 then it passes my check above, but those times clearly overlap for several hours.

I'm trying to find a solution to pull items even if they overlap, not JUST if their start and end times meet a certain check.

Any help is appreciated.

Upvotes: 2

Views: 3475

Answers (1)

Jane
Jane

Reputation: 438

Don't fully understand your question but will attempt to answer it anyway. For date overlaps I see four scenarios where an overlap would occur. Consider A as your event time span and B as the search param time span

1)
   A1          A2
B1       B2


2)
   A1          A2
         B1       B2


3)
   A1          A2
B1                B2


4)
   A1          A2
       B1  B2

Translated to comparison it would be

  • 1) B1 < A1 AND B2 > A1
  • 2) B1 > A1 AND B1 < A2
  • 3) B1 < A1 AND B2 > A1
  • 4) B1 > A1 AND B1 < A2

1,3 are the same as well as 2,4

And in laravel this should suffice

$query->where(function ($query) {
    $query->where([
        [$B1, '<', $A1],
        [$B2, '>', $A1],
    ])->orWhere([
        [$B1, '>', $A1],
        [$B1, '<', $A2],
    ]);
});

As an sql statement this would be

where (($B1 < $A1 and $B2 > $A1) or ($B1 > $A1 and $B1 < $A2))

Upvotes: 14

Related Questions