Laravel Eloquent return count of each group with groupBy()

Question

I want to groupBy() task using Laravel Eloquent. I have searched Internet and didn't find anything with eloquent. some people used groupBy() query with Query Builder like this link

But I want to create query like this style:

Task::select('id')->groupBy('category_id')->count();

This code just return only count of first category_id. But I want count of all the category_id.

Solution 1

Native Collection alternative (grouped by php, not mysql). It's a nice option when you have the Collection as variable already assigned. Of course is less optimal than mysql grouping in most cases.

$tasks->groupBy('category_id')->map->count();

Solution 2

You should add the count to the select function and use the get function to execute the query.

Task::select('id', \DB::raw("count(id)"))->groupBy('category_id')->get();

The \DB::raw() function makes sure the string is inserted in the query without Laravel modifying its value.

Solution 3

More simple:

Task::select('id')->groupBy('category_id')**->get()**->count();

Solution 4

I overrode the count() method in my model. Could be something like:

public function count($string = '*'){
    $tempmodel = new YourModel();
    return $tempmodel ->where('id',$this->id)->where('category_id',$this->category_id)->count($string);
}

This gave me exactly the behavior of count() I was looking for.

View more solutions

Comments

[sha-1]

Changing \DB::raw("count(id)") to \DB::raw("count(id) as total_count") would be more useful for further operation.

[Simas Joneliunas]

please consider explaining what have you changed to make it work

[Marek Gralikowski]

If you ask about map - laravel.com/docs/7.x/collections#higher-order-messages

[Bilal Baraz]

This is helpful for my problem. It works by single line.