How to combine two counts using Laravel

Using Laravel 8 for a restful API. A table in the database has following fields

report_date , supervisor_id and supervisor_note

I want to retrieve a result that counts the number of reports on any given day date and also counts all of the null values in supervisor_notes field.

I am using the following Laravel code:

$reports=DB::table('reports')
        ->where('supervisor_id','like', '%'.$supervisor_id.'%')
        ->select('report_date', DB::raw('count(*) as total'))
        ->groupBy('report_date')
        ->get();

I don’t know how to combine the code with the second count condition in the same query

Answer

You can use conditional aggregation here

$reports=DB::table('reports')
        ->where('supervisor_id','=', $supervisor_id)
        ->select([
            'report_date',
            DB::raw('count(*) as total'),
            DB::raw('sum(case when supervisor_note is null or supervisor_note = "" then 1 else 0 end) as notes_total')
        ])
        ->groupBy('report_date')
        ->get();

Also if supervisor_id is integer column and $supervisor_id also holds integer value then use exact integer matching like = instead of using string comparison using like clause