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?
This is working for me:
$user_info = DB::table('usermetas')
->select('browser', DB::raw('count(*) as total'))
->groupBy('browser')
->get();
This works for me (Laravel 5.1):
$user_info = Usermeta::groupBy('browser')->select('browser', DB::raw('count(*) as total'))->get();
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');
If you want to get collection, groupBy and count:
$collection = ModelName::groupBy('group_id')
->selectRaw('count(*) as total, group_id')
->get();
Cheers!
Open config/database.php Find strict key inside mysql connection settings Set the value to false
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');
Try with this
->groupBy('state_id','locality')
->havingRaw('count > 1 ')
->having('items.name','LIKE',"%$keyword%")
->orHavingRaw('brand LIKE ?',array("%$keyword%"))
havingRaw
bit is really nice since it lets you also lets you filter out the results you don't want based on the count. Comes in handy when you want to do some "and-filtering" and have joins for each filter.
$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.
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();
Another way would be this:
$data = Usermeta::orderBy('browser')->selectRaw('browser, count(*) as total')->get()
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
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();
}
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();
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
Success story sharing
DB::table('usermetas')->..
overUsermeta::..
?