- 1. The count() Method
- 2. The max() Method
- 3. The min() Method
- 4. The avg() or average() Method
- 5. The sum() Method
- 6. The value() Method
- 7. Aggregate Methods with Conditions
- 8. Advanced Aggregations: groupBy() and having()
- 9. Raw Aggregate Methods
Laravel's Eloquent ORM provides a rich set of tools for interacting with databases. Among these tools are aggregation methods, which allow developers to easily calculate totals, averages, and other aggregates directly from their models. In this article, we'll explore all of the available aggregation methods provided by Eloquent, diving into their usage and benefits.
Throughout this tutorial we'll try and use similar examples of employee salary queries.
Easily one of the most frequently used aggregation methods,
count() lets you determine the number of records in a table.
$usersCount = User::count();
Retrieve the highest value from a specific column using the
$highestSalary = Employee::max('salary');
Conversely, to find the smallest value in a column, employ the
$lowestSalary = Employee::min('salary');
Compute the average value of a specific column with the
avg() or its alias
$averageSalary = Employee::avg('salary');
When you need the combined total of a column, turn to the
$totalEarnings = Order::sum('amount');
For situations where you expect a single result from your query, the
value() method can be used. It retrieves the value of the first result of the query.
$name = User::where('name', 'John')->value('name');
7. Aggregate Methods with Conditions
You're not restricted to using these methods on entire tables. Combine them with other query builder methods for more specific aggregations.
$totalEarningsLastMonth = Order::whereMonth('created_at', now()->subMonth()->month) ->sum('amount');
8. Advanced Aggregations:
For more advanced aggregation needs, use the
$monthlyEarnings = Order::selectRaw('MONTH(created_at) as month, sum(amount) as total') ->groupBy('month') ->having('total', '>', 1000) ->get();
9. Raw Aggregate Methods
While Eloquent provides a fluent interface for many of its features, sometimes you might need to use raw SQL expressions. Use the
DB::raw() method in conjunction with aggregate methods for such cases.
$averageDailyEarnings = Order::select(DB::raw('DAY(created_at) as day, AVG(amount) as average')) ->groupBy('day') ->get();
Laravel's Eloquent ORM provides a multitude of aggregation methods to simplify database interactions and calculations. Leveraging these methods in your Laravel applications enables efficient and expressive data computations, elevating the quality and clarity of your code.
Interested in proving your knowledge of this topic? Take the PHP Fundamentals certification.
Covering the required knowledge to create and build web applications in PHP.