How could I sum different column type using eloquent

Question

I have a table called wf_request has an attribute status[1, 2, 3], so I need to sum these attribute each in one column and all of them in one database query.

wf_request table :

wf_request_id,
name,
status

wf_request_id name status
1  A     1
2  B     2
3  C     1
4  D     3

and use eloquent like so to get the result:

wf_request::get()->select('wf_request_id',
            DB::raw('sum(wf_request_id when status = 1 then 0 END) as `medium`'),
            DB::raw('sum(wf_request_id when status = 2 then 0 END) as `small`')
        )->groupBy('wf_request_id')

so my question how could I get the result like so:

{
  medium: 2
  small:1
}

Is this possible to be run in one database query?

Does this answer your question? [Laravel Eloquent Sum of relation's column](https://stackoverflow.com/questions/21679678/laravel-eloquent-sum-of-relations-column) — Simone Rossaini, Jul 29 '20 at 10:05
it's good, but also it works just on one sum and I need three or two sums for each status @SimoneRossaini — Omda, Jul 29 '20 at 10:08

score 2 · Accepted Answer · answered Jul 29 '20 at 10:28

I have rewritten the code using Laravel's Eloquent way. Let know if this makes sense.

$statuses = wf_request::all()->groupBy('status');
$statuses->keys()->map(function($status) use ($statuses) {
      $total = $statuses[$status]->reduce(function($mem, $item) {
        return $mem + 1;
      }, 0);
      switch($status){
        case 1:
          $new_status = "medium";
        break;
        case 2:
          $new_status = "small";
        break;
        default:
          $new_status = "none";
        break;
      }
      return [$new_status => $total];
});

here is a perfect solution https://reinink.ca/articles/calculating-totals-in-laravel-using-conditional-aggregates — Omda, Jul 29 '20 at 11:22

How could I sum different column type using eloquent

1 Answers1