php - How to get results from the query if the selected date is between two dates?

Question

I am trying to get the results of the following query: return the users that are available on a date selected. Users have selected when they are not available and this is stored in a "availabilities" table with a start datetime and an end datetime. Here is what I have been trying without success... I keep on getting the wrong users back and also if they have set more than one unavailability time period it will return them too.

Try #1

User::whereHas('availabilities', function($q)use($selected_date_time)
{
    $q->where('unavailable_start_date', '>', $selected_date_time)
    ->where('unavailable_end_date', '>', $selected_date_time);
})->orWhereHas('availabilities', function($q)use($selected_date_time)
{
    $q->where('unavailable_start_date', '<', $selected_date_time)
    ->where('unavailable_end_date', '<', $selected_date_time);
})->with('availabilities')->get();

Try #2

User::whereHas('availabilities', function($q)use($selected_date_time)->whereHas('availabilities', function($q)use($selected_date_time)
{
    $q->whereRaw('? NOT BETWEEN `unavailable_start_date` AND `unavailable_end_date`', [$selected_date_time]);
})->get();

Here is the mysql query executed in the try #1 case:

 select * from `users` where `is_user` = ? and (select count(*) from `availabilities` where `availabilities`.`user_id` = `users`.`id` and `unavailable_start_date` > ? and `unavailable_end_date` > ?) >= 1 or (select count(*) from `availabilities` where `availabilities`.`user_id` = `users`.`id` and `unavailable_start_date` < ? and `unavailable_end_date` < ?) >= 1

And the dates vardumped:

selected string(19) "2014-11-13 11:00:00"

unavailableStart string(19) "2014-11-12 11:30:00"

unavailableEnd string(19) "2014-11-18 11:00:00"

Ant idea what's going wrong or how to approach that problem?

EDIT FULL QUERY

$photographers_available =  Photographer::where('is_photographer', '=', '1')
                            ->whereHas('studioAddress', function($q)use($city_id)
                                                                   {
                                                                        $q->where('city_id', '=', $city_id);
                                                                   })
                            ->orWhereHas('user', function($q)use($city_id)  // TK could reduce to 'user.address' I think
                                                                     { 
                                                                        $q->whereHas('address', function($q)use($city_id) 
                                                                                     {
                                                                                        $q->where('city_id', '=', $city_id);
                                                                                     });
                                                                     })
                           ->whereHas('stypesPhotographer', function($q)use($stype)
                                                                   {
                                                                        $q->where('shooting_type_id', '=', $stype);
                                                                   })
                            ->where(function ($q) use ($selected_date_time) {
                            $q->whereHas('availabilities', function($q)use($selected_date_time)
                            {
                                $q->where('unavailable_start_date', '>', $selected_date_time)
                                ->where('unavailable_end_date', '>', $selected_date_time);
                            })->orWhereHas('availabilities', function($q)use($selected_date_time)
                            {
                                $q->where('unavailable_start_date', '<', $selected_date_time)
                                ->where('unavailable_end_date', '<', $selected_date_time);
                            });
                            })
                            ->with('availabilities')
                            ->get();

After thinking, could the orWhereHas be the one making it go wrong?

Thanks!

Answer 1

User::whereHas('availabilities', function ($q) use ($dt) {
    $q->where('unavailable_start_date', '<=', $dt)
      ->where('unavailable_end_date', '>', $dt);
}, '=', 0)->get();

So we are kinda reversing this in a way. We are setting up the query inside whereHas to find the availabilities, unavailabilities, that are in this range. So we are querying for all unavailable records (in a way). We are then only grabbing users that dont have relationships that match this criteria, by using the other arguments to whereHas ( '=', 0 ).

So any users that have this relation that fall into this time frame are not returned, no matter how many availability records they have. If any of them qualify they wont make it through the filter.