Laravel Eloquent loads not null dates when null dates are requested

Question

I'm currently trying to implement an issue logging service, when a task is completed a date can be entered into the 'completed' column which I have a CRUD for.

The idea is that the user selects from a dropdown with the following values:

• 0 - Hide completed tasks
• 1 - Show only completed tasks
• 2 - Show both completed and in progress tasks

My Controller code:

public function all(Request $request){

   $result = IssueLog::where('deleted', '=', null)
   ->when($request->sys, function ($q) use($request){
      $q->where('system', $request->sys);
   })
   ->when($request->sub, function ($qw) use($request){
      $qw->where('sub_system', $request->sub);
   })
   ->when($request->complete, function ($qr) use($request){
     switch ($request->complete) {
       case 0:
         return $qr->whereNull('completed');
       case 1:
         return $qr->whereNotNull('completed');
      case 2:
         return $qr->whereNull('completed')
            ->orWhereNotNull('completed');
       default:
         return $qr->whereNull('completed');
     }
    })
   ->orderByRaw('updated_at DESC NULLS LAST')
   ->get();

   return $result;
}

Results from case 1 come back as expected however, case 0 and 2 seem to be exactly the same, where the dates that aren't null come through in case 0 where they shouldn't be. I've also made sure that before the function is run the correct value is passed into the request.

Answer 1

As you have a default clause in your inner switch control, I'm assuming that you want a fallback in case where the completed it not provided in the request.

Using when() means that you will conditionally apply a specific query. The first argument determines if it should be used or not - based on the "truthfulness" of the argument. 0 is a falsy value, so it will not run when that's the case (have a look at the official manual on booleans).

Instead, we can apply a where() clause with a closure. We can also clean it up, and re-use the same results for different conditions (such as the default and case 0). For case 2, you want to return both those that are completed, and those that aren't - so instead of applying whereNull('completed')->orWhereNotNull('completed'), you can just return the query itself.

I've also replaced where('deleted', '=', null) with whereNull('deleted'), as that is the correct approach to check if a column is NULL.

public function all(Request $request) 
{
    $result = IssueLog::whereNull('deleted')
        ->when($request->sys, function ($q) use ($request) {
            return $q->where('system', $request->sys);
        })
        ->when($request->sub, function ($qw) use ($request) {
            return $qw->where('sub_system', $request->sub);
        })
        ->where(function($query) use ($request) {
            switch ($request->complete) {
                case 1:
                    return $query->whereNotNull('completed');
                case 2:
                    return $query;
                case 0:
                default:
                    return $query->whereNull('completed');
            }
        })
        ->orderByRaw('updated_at DESC NULLS LAST')
        ->get();

    return $result;
}

You should also consider using $request->has("sys") (or something like !empty($request->sys)) instead of supplying the actual request value to the first argument of your when() methods. These two functions return a boolean value, and is more explicit than passing the entire value.