Laravel, MongoDB: Include events with "touching" start/end times in query

Question

I'm going to give a brief overview about the app configuration before I go into the query. I am running a Laravel 6 app using MongoDB through Jenssegers MongoDB plugin. So far I have not had to write a raw mongo query and I hope I won't have to for this one too, I mean, I have no idea how to achieve what I want through Eloquent even with SQL, so not sure if possible.

I have three models:

User - standard user details

Availability - Event type model with start_date, end_date, duration, user_id and other unrelated properties

Shift - Event type model with start_date, end_date, duration and other unrelated properties

A User can have many Availability

What I need to do now is for a given Shift, find users that are available to work in that time (from Shift's start_date to end_date). Now that is pretty simple, I do:

User::whereHas('availability', function($query) use ($shift_start, $shift_end) {
    $query->where('start_date', '<=', $shift_start)->where('end_date', '>=', $shift_end);
})->get();

All of this works fine, but the users want to be able to create availability like this too:

Availability 1: 21 Sep 2020 14:00 - 21 Sep 2020 23:59

Availability 2: 22 Sep 2020 00:00 - 22 Sep 2020 06:00

What this means is that these two events are kind of "chained" together even though they are two events. This user would technically be available from 21 Sep 2020 14:00 - 22 Sep 2020 06:00 so if the Shift's dates are start: 21 Sep 2020 18:00 end: 22 Sep 2020 04:00, the user should be returned as available for the shift. I have failed to find a way to include this edge-case into the query and it is what I need help with.

Answer 1

Can you try to make your query as below,

User::whereHas('availability', function($query) use ($shift_start, $shift_end) {
    $query->where('start_date', '>=', $shift_start)->where('end_date', '<=', $shift_end);
    $query->orwhereBetween('start_date',array($shift_start,$shift_end))
    $query->orWhereBetween('end_date',array($shift_start,$shift_start))
})->get();