CODEWITHSUNDEEP
laraveleloquentrelationship
general666
general666

Reputation: 1039

Laravel query with relationships and conditions

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

It generates this queries: enter image description here

When I loop $nav there are still pages with status = 0. I really don't understand why? Any suggestions?

Upvotes: 1

Views: 185

Answers (2)

general666
general666

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

Devon Bessemer
Devon Bessemer

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

Related Questions