0

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.

Fidan Jovanov
  • 13
  • 6

1 Answers1

0

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();
Dilip Hirapara
  • 14,810
  • 3
  • 27
  • 49
  • I have not thought of trying that. I will give it a try and post feedback, thanks. Also I take it you meant on the last line between end_date $shift_start, $shift_end? – Fidan Jovanov Sep 28 '20 at 12:05
  • Yes compare `orwhereBetween` with `start_date` and `end_date` – Dilip Hirapara Sep 28 '20 at 12:07
  • First of all I noticed I had a flaw in logic, `$query->where('start_date', '<=', $shift_start)->where('end_date', '>=', $shift_end);` Needed to reverse the operators here as the shift needs to be WITHIN the availability. I tried your solution and if I leave the above line of code as well, then I only get strict matches, as I had below. If I remove my line of code and only use the orWhereBetweens, I do get the desired periods but I also get events that have a gap in between. – Fidan Jovanov Sep 28 '20 at 13:45
  • It returned true for a user that had **Availability 1:** 21 Sep 2020 14:00 - 21 Sep 2020 23:59 **Availability 2:** 22 Sep 2020 00:00 - 22 Sep 2020 06:00 but also true for a user that had **Availability 1:** 21 Sep 2020 14:00 - 21 Sep 2020 22:00 **Availability 2:** 22 Sep 2020 01:00 - 22 Sep 2020 06:00 – Fidan Jovanov Sep 28 '20 at 13:55