Laravel Eloquent groupBy() AND also return count of each group
LaravelLaravel 4EloquentLaravel Problem Overview
I have a table that contains, amongst other columns, a column of browser versions. And I simply want to know from the record-set, how many of each type of browser there are. So, I need to end up with something like this: Total Records: 10; Internet Explorer 8: 2; Chrome 25: 4; Firefox 20: 4. (All adding up to 10)
Here's my two pence:
$user_info = Usermeta::groupBy('browser')->get();
Of course that just contains the 3 browsers and not the number of each. How can I do this?
Laravel Solutions
Solution 1 - Laravel
This is working for me:
$user_info = DB::table('usermetas')
->select('browser', DB::raw('count(*) as total'))
->groupBy('browser')
->get();
Solution 2 - Laravel
This works for me (Laravel 5.1):
$user_info = Usermeta::groupBy('browser')->select('browser', DB::raw('count(*) as total'))->get();
Solution 3 - Laravel
Thanks Antonio,
I've just added the lists
command at the end so it will only return one array with key and count:
> Laravel 4
$user_info = DB::table('usermetas')
->select('browser', DB::raw('count(*) as total'))
->groupBy('browser')
->lists('total','browser');
> Laravel 5.1
$user_info = DB::table('usermetas')
->select('browser', DB::raw('count(*) as total'))
->groupBy('browser')
->lists('total','browser')->all();
> Laravel 5.2+
$user_info = DB::table('usermetas')
->select('browser', DB::raw('count(*) as total'))
->groupBy('browser')
->pluck('total','browser');
Solution 4 - Laravel
If you want to get collection, groupBy and count:
$collection = ModelName::groupBy('group_id')
->selectRaw('count(*) as total, group_id')
->get();
Cheers!
Solution 5 - Laravel
- Open
config/database.php
- Find
strict
key insidemysql
connection settings - Set the value to
false
Solution 6 - Laravel
Works that way as well, a bit more tidy.
getQuery()
just returns the underlying builder, which already contains the table reference.
$browser_total_raw = DB::raw('count(*) as total');
$user_info = Usermeta::getQuery()
->select('browser', $browser_total_raw)
->groupBy('browser')
->pluck('total','browser');
Solution 7 - Laravel
Try with this
->groupBy('state_id','locality')
->havingRaw('count > 1 ')
->having('items.name','LIKE',"%$keyword%")
->orHavingRaw('brand LIKE ?',array("%$keyword%"))
Solution 8 - Laravel
Laravel Version 8
Removed the dependency of DB
$counts = Model::whereIn('agent_id', $agents)
->orderBy('total', 'asc')
->selectRaw('agent_id, count(*) as total')
->groupBy('agent_id')
->pluck('total','agent_id')->all();
Solution 9 - Laravel
$post = Post::select(DB::raw('count(*) as user_count, category_id'))
->groupBy('category_id')
->get();
This is an example which results count of post by category.
Solution 10 - Laravel
In Laravel 8 you can use countBy()
to get the total count of a group.
Check the documentation on the same. https://laravel.com/docs/8.x/collections#method-countBy
Solution 11 - Laravel
Here is a more Laravel way to handle group by without the need to use raw statements.
$sources = $sources->where('age','>', 31)->groupBy('age');
$output = null;
foreach($sources as $key => $source) {
foreach($source as $item) {
//get each item in the group
}
$output[$key] = $source->count();
}
Solution 12 - Laravel
If you want to get sorted data use this also
$category_id = Post::orderBy('count', 'desc')
->select(DB::raw('category_id,count(*) as count'))
->groupBy('category_id')
->get();
Solution 13 - Laravel
Simple solution(tested with Laravel 9 and Spatie/Permissions).
Controller:
//Get permissions group by guard name(3 in my case: web, admin and api)
$permissions = Permission::get()->groupBy('guard_name');
View:
@foreach($permissions as $guard => $perm)
<div class="form-group">
<label for="permission">Permissions ({{ ucfirst($guard) }}) {{ count($perm) }}</label>
<select name="permission[]" id="permission" class="form-control @error('permission') is-invalid @enderror" multiple>
@foreach($perm as $value)
<option value="{{ $value->id }}">{{ $value->name }}</option>
@endforeach
</select>
@error('permission')
<div class="invalid-feedback">
{{ $message }}
</div>
@enderror
</div>
@endforeach