group by doesnot work

hi laracasts i have SancofaUse Model and i wat to group the data by department and to count


$days_after_now = Carbon::now()->subDays($request->day);
            $new_registerde_member = SancofaUser::where('created_at','>=',$days_after_now)->groupBy('department')->get()->keyBy('department')->map(function($item,$key){

                 return   count($item); 
            });
            dd($new_registerde_member);

and it gives this result

  "health infomatics" => 1
    "medicine" => 1

but there are 32 medicine and 43 health infomatics in my table

realrandyallen

6 years ago

Level 44

Your return from the map is overwriting the value of $item, so you'd have to add to the $item then return it, something like:

SancofaUser::where('created_at', '>=', $days_after_now)
   ->groupBy('department')
   ->get()
   ->keyBy('department')
   ->map(function($item, $key) {
      $item['count'] = count($item);
   
      return $item;
   });

However, you'd be better of doing this with query builder rather than looping over the results afterwards, something like:

$data = DB::table("yourtablename")
   ->select("department", DB::raw("COUNT(*) as count"))
   ->groupBy("department")
   ->get()
   ->keyBy('department');

Please or to participate in this conversation.