Include events with “touching” start/end times in 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.

bugsysha

5 years ago

Level 61

I think that you should search for these edge cases and replace them with one cause everything else will cause you to have some other edge case that you haven't thought of.

I would have an observer and when user creates his new availability I would check if the user has an overlapping or availability that ends one minute before this new one starts.

Only that way I would be sure that there are no edge cases that would cause headaches like this.

fidanym

5 years ago

Level 2

Yeah, that is what I thought at first too, but a user can submit availability for the entire year, i.e. create a repeating event that repeats for an entire year. The main issue is if I merge one of the repeating events with another event sometime in the future, this event would need to be decoupled from the group of repeating events. Then it will no longer be changeable if the user wants to update all occurrences in the future.

Please or to participate in this conversation.