How can I execute the a sql query which contain count(id_field) and group by (f_1,f_2)in eloquent?

Question

I am trying to execute the next SQL query:

SELECT  sex, recuperated, count(case_id) as total_cases
FROM api.patients
GROUP BY recuperated, sex
ORDER BY total_cases DESC;

Which returns:

'41523', 'ANTIOQUIA', 'MEDELLIN'
'6066', 'ANTIOQUIA', 'BELLO'
'6056', 'AMAZONAS', 'LETICIA'
'3855', 'ANTIOQUIA', 'ITAGUI'

first in eloquent:

$query = Patient::query();    
$statistics[0] = $query
->select("sex", "recuperated", "count (case_id) as total_cases")
->orderBy("total_cases","desc")
->groupBy("recuperated")
->get();

it returns:

Illuminate\Database\QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total_cases' in 'order clause' (SQL: select count(*) as aggregate from patients group by recuperated, sex order by total_cases desc)

second in eloquent:

$query = Patient::query();    
$statistics[0] = $query
->select('sex','recuperated',DB::raw('count(case_id) AS   total_cases'))
        ->orderBy('total_cases','DESC')
        ->groupBy('recuperated')
        ->get();

I am using MySql and laravel 7 and PHP 8

Tried putting the DB::raw into the OrderBy instead? (Without the AS part) — Caius Jard
The 2nd way should work. The only reason you'd get the message you are getting is because you're using pagination and if you are that is an important detail to include — apokryfos

Sebastian Romero Laguna Sebastian Romero Laguna · Accepted Answer · 2021-07-11T23:21:25

This works for me now.

    $statistics[0] = $query
    ->select(DB::raw('count(*) as patient_count, state'))
    ->orderBy('patient_count')
    ->groupBy('state')
    ->get();

The response is:

[
{"patient_count":18,"state":"Grave"}
{"patient_count":30,"state":"Moderado"},
{"patient_count":472,"state":"N\/A"},
{"patient_count":2110,"state":"Fallecido"},
{"patient_count":76137,"state":"Leve"}
]

This is a picture of how it looks: Image URL

I am not using pagination, I am trying to response with information to build statistics although.

How can I execute the a sql query which contain count(id_field) and group by (f_1,f_2)in eloquent?

1 Answers