-5

I have a data table named CallTriggers with the attributs 

cloturer = 0 or 1
created_at

and I want to get all the data like this

$array = [
           ['Month', 'Total Call', 'Close'],
           ['Jan', 57, 50],
           ['Feb', 67, 60],
           ['Mar', 40, 40],
           ['Apr', 33, 30],
           ['May', 70, 66]
       ];

Example: ['Jan', 57, 50] here jan is the month, 57 is number of call in the month with value 1 and 50 is number of that the value is 0 in database.

How can I perform that kind of operation with Laravel 5.6? Thanks

BriscoPeas Aristide
  • 129
  • 2
  • 10

2 Answers2

2

Model

class CallTrigger extends Model
{
    protected $table = 'CallTriggers';
}

Controller function

$triggers = CallTrigger::all()
    ->groupBy(function($callTrigger) {
        return $callTrigger->created_at->format('M') .'-' . $callTrigger->created_at->format('Y');
    })
    ->map(function($group, $groupName) {
        return [
            $groupName,
            $group->where('cloturer', 1)->count(),
            $group->where('cloturer', 0)->count(),
        ];
    })
    ->values();
Tharaka Dilshan
  • 4,371
  • 3
  • 14
  • 28
-1

You need to execute a raw query:

SELECT 
  DATE_FORMAT(`created_at`, '%b') AS `Month`, 
  SUM(IF(`cloturer` = 1, 1, 0)) AS `Total Call`, 
  SUM(IF(`cloturer` = 0, 1, 0)) AS `Close` 
FROM 
  `CallTriggers` 
GROUP BY 
  MONTH(`created_at`), YEAR(`created_at`);

You need to disable ONLY_FULL_GROUP_BY mode in MySQL for this.

David Gobronidze
  • 19
  • 5