Hello!

I can't figure out why the mysql query is returning results when it shouldn't, here is the thing: I am trying to return results for users that are not unavailable during a time period. So I have the table availabilities unavailable start datetime and unavailable end datetime with a foreign key to the user. Now I have the selected datetime I am trying to test.

Here is the query:

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();

Here is the result of the logical test made manually on the user returned to see if it the test that is wrong or a problem in the query:

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

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

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

$undateS > $selected_date_time : result = bool(false)
$undateE > $selected_date_time : result = bool(true)  ==> two above && return false
$undateS < $selected_date_time : result = bool(true)
$undateE < $selected_date_time : result = bool(false)  ==> two above && return false

As you can see the OR test will return a FALSE, meaning that this user should not show up in the results... strangely enough I have another user that is also set as unavailable for a time period similar and he will not be returned in the results.....

What am I doing wrong on this?

ps: the SQL query executed:

     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

Thanks!