CODEWITHSUNDEEP
laravelwhere-clause
Arjun
Arjun

Reputation: 818

Laravel: How to apply where condition only when the field value is not NULL

I am trying to write query in Laravel:

 return DB::table('course_modules')->select('module_id', 'publish_from', 'publish_to')
                    ->where('course_id', $course_id)
                    ->where('is_deleted', '0')
                 ->orwhere('publish_from', '>=', date('Y-m-d H:i:s'))
                    ->orwhere('publish_to', '<=', date('Y-m-d H:i:s'))
                    ->orwhere(function($query) {
                                $query->where('publish_from', '>=', date('Y-m-d H:i:s'))
                                ->where('publish_to', '<=', date('Y-m-d H:i:s'));
                            })
                    ->where('hide_module', 1)
                    ->get();

I want to apply where clause on publish_to and publish_from only when that fields are not NULL.

I want to do like this:

 if(publish_from!=NULL){   ->orwhere('publish_from', '>=', date('Y-m-d H:i:s'))}

And same way:

 if(publish_to!=NULL){   ->orwhere('publish_to', '>=', date('Y-m-d H:i:s'))}

What can I do to resolve this?

Upvotes: 3

Views: 2800

Answers (1)

lukasgeiter
lukasgeiter

Reputation: 153140

You have to rethink your logic a bit, as SQL statements aren't very good with conditional wheres... So what you actually want is publish_from and publish_to to be either NULL or in a certain range.

This should accomplish that:

return DB::table('course_modules')->select('module_id', 'publish_from', 'publish_to')
    ->where('course_id', $course_id)
    ->where('is_deleted', '0')
    ->where('hide_module', 1)
    ->where(function($q){
        $q->where(function($q){
            $q->whereNull('publish_from')
              ->whereNull('publish_to');
        })->orWhere(function($q){
            $q->where('publish_from', '>=', date('Y-m-d H:i:s'))
              ->where('publish_to', '<=', date('Y-m-d H:i:s'));
        });
    })
    ->get();

Note that you don't necessarily need two levels of nested closures here, but it makes it easier to read and you avoid mistakes.

Also, you can switch out date('Y-m-d H:i:s') for Carbon::now(), which is a bit nicer if you ask me.

Upvotes: 5

Related Questions