Reputation: 1039
Hi I have 3 tables in DB. Here is my query:
$nav = PageNav::with([
'page' => function($query) {
$query->where('status', 1)
->where(function($query) {
$query->whereNull('publish_up')
->whereNull('publish_down');
})
->orWhere(function ($query) {
$query->where('publish_up', '<', date('Y-m-d H:i:s'))
->where('publish_down', '>', date('Y-m-d H:i:s'));
});
},
'langs' => function ($query) use ($lang) {$query->where('language_code', '=', $lang);},
])
->get();
When I loop $nav there are still pages with status = 0. I really don't understand why? Any suggestions?
Upvotes: 1
Views: 185
Reputation: 1039
Ok I found solution. Needed to wrap condition to whereHas
$nav = PageNav::whereHas('page', function ($query) {
$query->where('status', 1)
->whereNested(function($q1) {
$q1->where(function($query) {
$query->whereNull('publish_up')
->whereNull('publish_down');
})
->orWhere(function ($query) {
$query->where('publish_up', '<', date('Y-m-d H:i:s'))
->where('publish_down', '>', date('Y-m-d H:i:s'));
});
});
})
->with([
'page',
'langs' => function ($query) use ($lang) {
$query->where('language_code', '=', $lang);
}
])
->get();
Upvotes: 0
Reputation: 35377
As with any SQL query or Eloquent query, OR
completely separates the conditions, so as soon as you have an orWhere
in eloquent, it will match anything that matches before the OR or after the OR.
You can resolve this by using a nested where, or parentheses in SQL, to properly encapsulate the conditions.
$query->where('status', 1)
->whereNested(function($q1) {
$q1->where(function($query) {
$query->whereNull('publish_up')
->whereNull('publish_down');
})
->orWhere(function ($query) {
$query->where('publish_up', '<', date('Y-m-d H:i:s'))
->where('publish_down', '>', date('Y-m-d H:i:s'));
});
});
Upvotes: 4