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
Advertisement
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