Simplified Grouped Aggregates in Laravel Query Builder


IMPORTANT NOTE: THIS PULL REQUEST WAS REVERTED ONCE AGAIN BECAUSE OF BREAKING CHANGES.
New in Laravel: Aggregate By Group
Introducing grouped aggregate methods, a noteworthy enhancement to Laravel's database query builder with Pull Request #53679, contributed by GromNaN. This update adds new methods for performing grouped aggregate calculations, making database queries cleaner, more expressive, and easier to maintain.
Understanding the Problem
Before this update, performing aggregate operations like count
, sum
, min
, max
, and avg
required a combination of groupBy
and raw expressions. While effective, this approach was often verbose and less intuitive. For developers working on large or complex applications, such queries could quickly become error-prone and harder to read.
Consider this example:
$salesByCategory = DB::table('sales') ->select('category_id', DB::raw('SUM(amount) as total_sales')) ->groupBy('category_id') ->get();
While functional, this code involves raw expressions and manual grouping, which might confuse newcomers or lead to maintenance challenges.
Introducing Grouped Aggregate Methods
With the new methods, Laravel simplifies grouped aggregate operations by introducing the following:
-
countByGroup
-
minByGroup
-
maxByGroup
-
sumByGroup
-
avgByGroup
These methods streamline database queries by abstracting common patterns into expressive, reusable methods.
Example Usage
Hereβs how you can rewrite the previous example using the new sumByGroup
method:
$salesByCategory = DB::table('sales') ->groupBy('category_id') ->sumByGroup('amount');
This single line of code performs the same operation but is more readable and less prone to errors. It communicates intent clearly, adhering to Laravel's philosophy of elegant syntax.
A Closer Look at Each Method
-
countByGroup
: Counts the number of records in each group.$counts = DB::table('users')->groupBy('status')->countByGroup(); -
minByGroup
: Finds the minimum value in each group.$minSalaries = DB::table('employees')->groupBy('department_id')->minByGroup('salary'); -
maxByGroup
: Determines the maximum value in each group.$maxPrices = DB::table('products')->groupBy('category_id')->maxByGroup('price'); -
sumByGroup
: Calculates the sum of values in each group.$totalSales = DB::table('orders')->groupBy('region_id')->sumByGroup('amount'); -
avgByGroup
: Computes the average value for each group.$avgRatings = DB::table('reviews')->groupBy('product_id')->avgByGroup('rating');
Stay Updated.
I'll you email you as soon as new, fresh content is published.