Return Mulitple count of items in Eloquent

Got a simple, yet confusing question here.

I have a list of students. Is it possible to get the total number of students and also the total number of students not at age of 8, 9, 10 at the same time?

$students = Student::select(DB::raw('COUNT('id') as all'))->get();

I also need to get the count of ->whereNotIn('age', [8,9,10]). How do I proceed?

karllson

8 years ago

Best Answer

Level 6

You maybe find an approach using case when

SELECT 
    COUNT(id) as total,
    SUM(CASE WHEN age IN (8, 9, 10) THEN 1 ELSE 0 END) as youngerTenCount,
    SUM(CASE WHEN age NOT IN(8, 9, 10) THEN 1 ELSE 0 END) as olderTenCount
FROM students

So you basically will have three DB::raws in your select

Student::select([
    DB::raw("COUNT(id) as total"),
    DB::raw("SUM(CASE WHEN age IN (8, 9, 10) THEN 1 ELSE 0 END) as youngerTenCount"),
    DB::raw("SUM(CASE WHEN age NOT IN(8, 9, 10) THEN 1 ELSE 0 END) as olderTenCount")
]);

Not tested.

Hopefully understood you correctly.

1 like

richard

8 years ago

Level 21

Works like charm. Thanks bro

1 like

Please or to participate in this conversation.