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
– Caius Jard, Commented Jul 11, 2021 at 6:02
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
– apokryfos, Commented Jul 11, 2021 at 6:33

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

0

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.

answered Jul 11, 2021 at 23:21

Sebastian Romero Laguna

314 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related