CODEWITHSUNDEEP
phplaraveleloquent
Omda
Omda

Reputation: 177

How could I sum different column type using eloquent

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?

Upvotes: 0

Views: 72

Answers (1)

Vasanth Gopal
Vasanth Gopal

Reputation: 1285

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];
});

Upvotes: 2

Related Questions