Posted inphp / Laravel / Mysql / Mysqli

Laravel Group By Clause/Statement

This tutorial will give you guide of laravel collection group by relationship, array, date and row example. Here you will know laravel collection groupby two columns. you will understand step by step laravel collection group by with relationship, sum, date and array. you can show laravel collection group by with total count with alias.

I will give you very simple guide of laravel collection with two columns, map, sum, count, date, preserve key etc.

laravel group by Examples

I will give you list of examples of groupby colletion in laravel. so you can easily use it with your laravel 5/6/7 application. so let’s show bellow guide that will helps you lot.

So this PHP Laravel GROUP BY Statement is about a new feature in Laravel. But before we check that out, let’s show how it worked before. groupBy with by multiple, by collection, by raw, by relationship, by array and by sum are a method of the Collection class.

Simple groupBy

here in step first, let me Remember you a characteristic groupBy usage – for below simple Database: Query Builder example, if you want to group students by student:

$results = Student::select('student', \DB::raw('COUNT(id) as salary'))
    ->groupBy('student')
    ->get();

foreach ($results as $result) {
    echo $result->student . ': ' . $result->salary . '
'; }

groupBy with Raw Condition

In second step, what if you want to group by register year? It below Database: Query Builder like this:

$results = Student::select(\DB::raw('YEAR(register_date) as year, COUNT(id) as salary'))
    ->groupBy(\DB::raw('YEAR(register_date)'))
    ->get();

Therefor, I am using DB::raw() for Database: Query Builder all select statement, also repeating the same like condition in groupBy().

groupBy Raw with Boolean Condition

Now I am retrieving to the Best tips and tricks of the tutorial. How to group by condition of Join before or after 2010 year? Therefor result would return simple two rows. Here’s the full Database: Query Builder source code:

$results = Student::select(\DB::raw('YEAR(register_date) < 2010 as adult, COUNT(id) as salary'))
    ->groupBy(\DB::raw('YEAR(register_date) < 2010'))
    ->get();

group by multiple

Every Devlopers we may require to included group by with multiple level columns, if we have mysql query then I can do it easily by using sql query. But if you want to give multiple columns in groupBy() of Laravel Query Builder then you can give by comma separated values as bellow Database: Query Builder example.

Group by multiple columns in Laravel Example

$teachers = DB::table('teacher')
->groupBy('teacher_origin', 'teacher_destination')
->get();

group by multiple Table columns

$data = DB::table("students_count")
	        ->select(
	            'students_count.*'
	            ,DB::raw("SUM(students_count.rating) as total_marks"))
	        ->groupBy('students_count.id_item','students_count.id_cat')
	        ->get();
print_r($data);   

Laravel Collection Group By with Multiple Columns

public function index()
{
    $collection = collect([
            ['id'=>1, 'name'=>'Donald Trump', 'student' => 'whatsapp', 'teacher' => 'TamilRokers'],
            ['id'=>2, 'name'=>'Narendra Modi', 'student' => 'pepega', 'teacher' => 'Bolly4u'],
            ['id'=>3, 'name'=>'Frank-Walter Steinmeier', 'student' => 'khatrimaza', 'teacher' => 'TamilRokers'],
            ['id'=>4, 'name'=>'Xi Jinping', 'student' => 'pepega', 'teacher' => 'Bolly4u'],
        ]);
  
    $grouped = $collection->groupBy(function ($item, $key) {
                    return $item['teacher'].$item['student'];
                });
    
    dd($grouped);
}

Results:

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [TamilRokerswhatsapp] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 1
                                    [name] => Donald Trump
                                    [student] => whatsapp
                                    [teacher] => TamilRokers
                                )
                        )
                )
            [Bolly4upepega] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 2
                                    [name] => Narendra Modi
                                    [student] => pepega
                                    [teacher] => Bolly4u
                                )
                            [1] => Array
                                (
                                    [id] => 4
                                    [name] => Xi Jinping
                                    [student] => pepega
                                    [teacher] => Bolly4u
                                )
                        )
                )
            [TamilRokerskhatrimaza] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 3
                                    [name] => Frank-Walter Steinmeier
                                    [student] => khatrimaza
                                    [teacher] => TamilRokers
                                )
                        )
                )
        )
)

group by collection

If you eager-load litanswers, then you can group the resulting collection by the nested object of the collection. So you may have:

$charges = Expenses::with('litanswers')->get(); //collection

Then group it:

$grouped = $charges->groupBy('litanswers.name'); 
//or
$grouped = $charges->groupBy('litanswer_id');

Then you can use the name of litanswers, accessing it, say we need the first group, and the first charge in the group,

$litanswer_name = $grouped
->first() //the first group
->first() //the first charge in group
->litanswers //the litanswers object
->name //the name

group by raw

use group by having with DB::raw in Laravel Query Builder?

$teachers = DB::table("teachers")
            ->select("teachers.id","teachers.title"
                ,"teachers.min_rating"
                ,DB::raw('SUM(students_count.rating) as total_rating'))
            ->join("students_count","students_count.id_item","=","teachers.id")
            ->groupBy("teachers.id")
            ->having("total_rating","<",DB::raw("teachers.min_rating"))
            ->get();
dump($teachers);

Results

Array
(
    [0] => stdClass Object
        (
            [id] => 2
            [title] => Pakainfo.com
            [min_rating] => 10
            [total_rating] => 5
        )
)

group by date

Laravel Collection Group By with Date

public function index()
{
    $collection = collect([
            ['id'=>1, 'name'=>'Donald Trump', 'created_at' => '2021-03-10 10:10:00'],
            ['id'=>2, 'name'=>'Narendra Modi', 'created_at' => '2021-03-10 10:14:00'],
            ['id'=>3, 'name'=>'Frank-Walter Steinmeier', 'created_at' => '2021-03-11 10:12:00'],
            ['id'=>4, 'name'=>'Xi Jinping', 'created_at' => '2021-03-12 10:12: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);
}

Results

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [03/10/2021] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 1
                                    [name] => Donald Trump
                                    [created_at] => 2021-03-10 10:10:00
                                )
                            [1] => Array
                                (
                                    [id] => 2
                                    [name] => Narendra Modi
                                    [created_at] => 2021-03-10 10:14:00
                                )
                        )
                )
            [03/11/2021] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 3
                                    [name] => Frank-Walter Steinmeier
                                    [created_at] => 2021-03-11 10:12:00
                                )
                        )
                )
            [03/12/2021] => Illuminate\Support\Collection Object
                (
                    [items:protected] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 4
                                    [name] => Xi Jinping
                                    [created_at] => 2021-03-12 10:12:00
                                )
                        )
                )
        )
)

group by relationship

Relationship methods allow us to call the count() method on them to get the relationship count on the Books.

class Book extends Eloquent
{
    public function books()
    {
        return $this->hasMany(Booking::class);
    }
}

// ...

$book->books()->count(); // number of related books

object to keep in mind here is that every time you call the count() method the laravel web application will run or execute the database table.

$book->books()->count();
$book->books()->count();
$book->books()->count();
// total count database runs: 3
class Book extends Eloquent
{
    public function books()
    {
        return $this->hasMany(Booking::class);
    }

    protected function getBookingsCountAttribute($value)
    {
        return $value ?? $this->books_count = $this->books()->count();
    }
}

// ...

$book->books_count;
$book->books_count;
// total database runs: 1
public function show($id)
{
    $book = Book::withCount('books')->findOrfail($id); // explicitly call withCount()

    // ...
}
public function show(Book $book)
{
    $book->books_count; // already available

    // ...
}

group by count

public function index()
{
    $collection = collect([
            ['id'=>1, 'name'=>'Donald Trump', 'student' => 'whatsapp', 'teacher' => 'TamilRokers'],
            ['id'=>2, 'name'=>'Narendra Modi', 'student' => 'pepega', 'teacher' => 'Bolly4u'],
            ['id'=>3, 'name'=>'Frank-Walter Steinmeier', 'student' => 'khatrimaza', 'teacher' => 'TamilRokers'],
            ['id'=>4, 'name'=>'Xi Jinping', 'student' => 'pepega', 'teacher' => 'Bolly4u'],
        ]);
  
    $grouped = $collection->groupBy('teacher')->map(function ($row) {
                    return $row->count();
                });
  
    dd($grouped);
}

Results:

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [TamilRokers] => 2
            [Bolly4u] => 2
        )
)

group by sum

Laravel Collection Group By with Sum

public function index()
{
    $collection = collect([
            ['id'=>1, 'name'=>'Donald Trump', 'student' => 'whatsapp', 'teacher' => 'TamilRokers', 'salary' => 2000],
            ['id'=>2, 'name'=>'Narendra Modi', 'student' => 'pepega', 'teacher' => 'Bolly4u', 'salary' => 1000],
            ['id'=>3, 'name'=>'Frank-Walter Steinmeier', 'student' => 'khatrimaza', 'teacher' => 'TamilRokers', 'salary' => 3000],
            ['id'=>4, 'name'=>'Xi Jinping', 'student' => 'pepega', 'teacher' => 'Bolly4u', 'salary' => 2000],
        ]);
  
    $grouped = $collection->groupBy('teacher')->map(function ($row) {
                    return $row->sum('salary');
                });
   
    dd($grouped);
}

Results

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [TamilRokers] => 5000
            [Bolly4u] => 3000
        )
)
Web Programming Tutorials Example with Demo

Read :

Summary

You can also read about AngularJS, ASP.NET, VueJs, PHP.

I hope you get an idea about laravel group by.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this tutorial are always welcome.
If you enjoyed and liked this post, don’t forget to share.

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Leave a Reply

Your email address will not be published. Required fields are marked *

We accept paid guest Posting on our Site : Guest Post Chat with Us On Skype