Laravel’s groupBy method is a powerful feature that allows developers to efficiently group query results based on a specific column or attribute. In this tutorial, you will learn how to use group by with different purpose in laravel.
As well as you learn how to use laravel group with raw, laravel group by multiple, laravel group by date, laravel group by sum, laravel group by month and laravel collection group by count.
Sometimes, you may confuse when use group by with laravel eloquent queries. So this tutorial will guide you how to use laravel group by with different purpose with example.
Laravel Group By Examples
Here are following examples of laravel group by eloquent method:
- 1: Laravel GroupBy with Collection Example
- 2: Laravel Collection Group By Preserve Key
- 3: Laravel Collection Group By with Multiple Columns
- 4: Laravel Collection Group By with Date
- 5: Laravel Collection Group By with Count
- 6: Laravel Collection Group By with Sum
1: Laravel GroupBy with Collection Example
public function index()
{
$collection = collect([
'first' => ['id'=>1, 'name'=>'Rahul', 'city' => 'Mumbai', 'country' => 'India'],
'second' => ['id'=>2, 'name'=>'Sumit', 'city' => 'New York', 'country' => 'US'],
'third' => ['id'=>3, 'name'=>'Ronak', 'city' => 'Gujarat', 'country' => 'India'],
'fourth' => ['id'=>4, 'name'=>'Harish', 'city' => 'New York', 'country' => 'US'],
]);
$grouped = $collection->groupBy('country');
dd($grouped);
}
Result:
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[India] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 1
[name] => Rahul
[city] => Mumbai
[country] => India
)
[1] => Array
(
[id] => 3
[name] => Sumit
[city] => Gujarat
[country] => India
)
)
)
[US] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 2
[name] => Ronak
[city] => New York
[country] => US
)
[1] => Array
(
[id] => 4
[name] => Harish
[city] => New York
[country] => US
)
)
)
)
)
2: Laravel Collection Group By Preserve Key
here, we will use same example as above but we will pass preserve key as true. so you can compare both output and see. there is difference is “key”, here will be key name same.
$collection->groupBy('Key_Name', $preserve_key);
public function index()
{
$collection = collect([
'first' => ['id'=>1, 'name'=>'Rahul', 'city' => 'Mumbai', 'country' => 'India'],
'second' => ['id'=>2, 'name'=>'Sumit', 'city' => 'New York', 'country' => 'US'],
'third' => ['id'=>3, 'name'=>'Ronak', 'city' => 'Gujarat', 'country' => 'India'],
'fourth' => ['id'=>4, 'name'=>'Harish', 'city' => 'New York', 'country' => 'US'],
]);
$grouped = $collection->groupBy('country', True);
dd($grouped);
}
Result:
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[India] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[first] => Array
(
[id] => 1
[name] => Rahul
[city] => Mumbai
[country] => India
)
[third] => Array
(
[id] => 3
[name] => Ronak
[city] => Gujarat
[country] => India
)
)
)
[US] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[second] => Array
(
[id] => 2
[name] => Sumit
[city] => New York
[country] => US
)
[fourth] => Array
(
[id] => 4
[name] => Harish
[city] => New York
[country] => US
)
)
)
)
)
3: Laravel Collection Group By with Multiple Columns
public function index()
{
$collection = collect([
'first' => ['id'=>1, 'name'=>'Rahul', 'city' => 'Mumbai', 'country' => 'India'],
'second' => ['id'=>2, 'name'=>'Sumit', 'city' => 'New York', 'country' => 'US'],
'third' => ['id'=>3, 'name'=>'Ronak', 'city' => 'Gujarat', 'country' => 'India'],
'fourth' => ['id'=>4, 'name'=>'Harish', 'city' => 'New York', 'country' => 'US'],
]);
$grouped = $collection->groupBy(function ($item, $key) {
return $item['country'].$item['city'];
});
dd($grouped);
}
Result:
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[IndiaMumbai] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 1
[name] => Rahul
[city] => Mumbai
[country] => India
)
)
)
[USNew York] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 2
[name] => Sumit
[city] => New York
[country] => US
)
[1] => Array
(
[id] => 4
[name] => Harish
[city] => New York
[country] => US
)
)
)
[IndiaGujarat] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 3
[name] => Ronak
[city] => Gujarat
[country] => India
)
)
)
)
)
4: Laravel Collection Group By with Date
public function index()
{
$collection = collect([
['id'=>1, 'name'=>'Rahul', 'created_at' => '2020-05-15 15:15:00'],
['id'=>2, 'name'=>'Sumit', 'created_at' => '2020-05-15 15:16:00'],
['id'=>3, 'name'=>'Ronak', 'created_at' => '2020-05-18 15:18:00'],
['id'=>4, 'name'=>'Harish', 'created_at' => '2020-05-19 15:10:00'],
]);
$grouped = $collection->groupBy(function($item, $key) {
return \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $item['created_at'])->format('m/d/Y');
});
dd($grouped);
}
Result:
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[05/15/2020] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 1
[name] => Rahul
[created_at] => 2020-05-15 15:15:00
)
[1] => Array
(
[id] => 2
[name] => Sumit
[created_at] => 2020-05-15 15:16:00
)
)
)
[05/18/2020] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 3
[name] => Ronak
[created_at] => 2020-05-18 15:18:00
)
)
)
[05/19/2020] => Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 4
[name] => Harish
[created_at] => 2020-05-19 15:10:00
)
)
)
)
)
5: Laravel Collection Group By with Count
public function index()
{
$collection = collect([
['id'=>1, 'name'=>'Rahul', 'created_at' => '2020-05-15 15:15:00'],
['id'=>2, 'name'=>'Sumit', 'created_at' => '2020-05-15 15:16:00'],
['id'=>3, 'name'=>'Ronak', 'created_at' => '2020-05-18 15:18:00'],
['id'=>4, 'name'=>'Harish', 'created_at' => '2020-05-19 15:10:00'],
]);
$grouped = $collection->groupBy('country')->map(function ($row) {
return $row->count();
});
dd($grouped);
}
Result:
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[India] => 2
[US] => 2
)
)
6: Laravel Collection Group By with Sum
public function index()
{
$collection = collect([
['id'=>1, 'name'=>'Rahul', 'created_at' => '2020-05-15 15:15:00'],
['id'=>2, 'name'=>'Sumit', 'created_at' => '2020-05-15 15:16:00'],
['id'=>3, 'name'=>'Ronak', 'created_at' => '2020-05-18 15:18:00'],
['id'=>4, 'name'=>'Harish', 'created_at' => '2020-05-19 15:10:00'],
]);
$grouped = $collection->groupBy('country')->map(function ($row) {
return $row->sum('amount');
});
dd($grouped);
}
Result:
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[India] => 5000
[US] => 3000
)
)
Conclusion
In this tutorial, you have learned how to use a group by with a different purpose in laravel eloquent queries.
Recommended Laravel Tutorials
Recommended:-Laravel PHP Artisan Serve Not Working
Recommended:-Laravel Get Record Last Week, Month, 15 Days, Year
Recommended:-How to Set or Increase Session Lifetime in Laravel
Recommended:-Laravel where Day, Date, Month, Year, Time, Column
Recommended:-Laravel Try Catch
Recommended:-Laravel Eloquent whereRaw Query Example
Recommended:-How to Get Random Records in Laravel
Recommended:-Laravel InsertOrIgnore Example
Recommended:-Laravel whereIn, whereNotIn With SubQuery Example
Recommended:-Laravel Eloquent withSum() and withCount() Tutorial
Recommended:-Laravel Where Null and Where Not Null Query
Recommended:-Laravel Eloquent firstWhere() Example
Recommended:-Laravel Eloquent selectRaw Query Tutorial
Recommended:-Laravel Order by Example
Recommended:-Laravel orderByRaw() Query Example
Recommended:-Fetch Single Row Data from Database in Laravel
Recommended:-Laravel Disable Created_at and Updated_at timestamps
Recommended:-Laravel OneSignal Web Push Notification Example
Recommended:-Get Last 3, 6, 12 Months Data in Laravel
Recommended:-Laravel Download File From AWS s3 Bucket
Recommended:-Laravel Override Login Method
Recommended:-Laravel Connect Remote Database using SSH Tunnel
Recommended:-How to fix Error: laravel.log could not be opened?
