I have a table in MySQL where I store indexes, and I need to count a column of indices by bringing an alias for each count.
I tried the following code but it does not work, and as I am new to Laravel, I'm not sure how I can build this query
$data = Baralho::select(
DB::raw("YEAR(data_entrada) as ano"),
DB::raw("count(id_situacao) as inseridos, id_situacao"),
DB::raw("count(id_situacao = 2) as presos"),
DB::raw("count(id_situacao = 3) as mortos"),
DB::raw("count(id_situacao = 4) as retirados")
)->groupBy("ano")->get();
$geral[] = ['Ano', 'Inseridos', 'Presos', 'Mortos', 'Retirados'];
//$anos = $data->pluck('ano');
foreach($data as $key => $value){
$geral[++$key] = [$value->ano, $value->inseridos, $value->presos, $value->mortos, $value->retirados ];
}
dd($geral);
I need it to count the indexes and return a result for each alias, for example, if in the column id_situacao
the index is 2
it counts all 2 indexes and brings me the result with the alias presos
so that I can insert those results in my array $geral[]
Edit:
I tried with the following code:
$data = DB::select(
DB::raw('(SELECT YEAR(data_entrada) as ano FROM baralho)'),
DB::raw('(SELECT count(id_situacao) as inseridos FROM baralho)'),
DB::raw('(SELECT count(id_situacao) as presos FROM baralho WHERE id_situacao = 2)'),
DB::raw('(SELECT count(id_situacao) as mortos FROM baralho WHERE id_situacao = 3)'),
DB::raw('(SELECT count(id_situacao) as retirados FROM baralho WHERE id_situacao = 4)')
)->groupBy('ano')->get();;
$geral[] = ['Ano', 'Inseridos', 'Presos', 'Mortos', 'Retirados'];
//$anos = $data->pluck('ano');
//dd($anos);
foreach($data as $key => $value){
$geral[++$key] = [$value->ano, $value->inseridos, $value->presos, $value->mortos, $value->retirados];
//dd($value);
}
dd($geral);
but I received the error:
Symfony \ Component \ Debug \ Exception \ FatalThrowableError (E_RECOVERABLE_ERROR) Argument 1 passed to Illuminate\Database\Connection::prepareBindings() must be of the type array, object given, called in /home/u185216683/domains/disquedenuncia.com/sysdados/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 328