Mysql / Laravel Logical test returning false but query returning true

Question

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?

EDIT SQL 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 for your help!

Like here: http://stackoverflow.com/a/26886515/784588 – Jarek Tkaczyk Nov 12 '14 at 12:04 — Jarek Tkaczyk, Nov 12 '14 at 12:04

score -1 · Answer 1 · answered Nov 12 '14 at 03:44

-1

It seems like the User model was not triggering the right sql script. You could check what sql exactly the Eloquent Model triggered with this tool, barryvdh/laravel-debugbar

answered Nov 12 '14 at 03:44

JasonW

453
1
9
16

I can see the script via dd(DB::getQueryLog()); – commandantp Nov 12 '14 at 03:48

Mysql / Laravel Logical test returning false but query returning true

1 Answers1