Laravel Eloquent: Find MIN/MAX in each group

Introduction

Laravel’s Eloquent ORM is a powerful tool for interacting with your database using an expressive, fluent API to create, retrieve, update, and delete database records. Among many other things, Eloquent allows you to perform aggregation queries, such as finding the minimum (MIN) or maximum (MAX) value of a column in a database table, which can be very useful for reporting and analytics. This tutorial dives into how to perform these aggregation queries within groups using Eloquent.

Understanding Grouping in Eloquent

Before diving into MIN/MAX queries, it’s important to understand the concept of ‘grouping’ in the context of SQL queries. Grouping allows you to aggregate data by specific columns, offering a way to collapse many rows into one per group, based on the values of one or more columns. Let’s explore how to leverage this with Eloquent.

Finding MIN Value in a Group with Eloquent

Let’s start with a simple example. Suppose we have a ‘sales’ table, and we want to find the minimum sale amount made by each employee. In Eloquent, this could be done as follows:

$minSales = Sale::select('employee_id', DB::raw('MIN(amount) as min_amount'))
              ->groupBy('employee_id')
              ->get();

This will return a collection of the minimum sale amounts, grouped by employee.

Finding MAX Value in a Group with Eloquent

To find the maximum sale amount by each employee, you use a similar approach:

$maxSales = Sale::select('employee_id', DB::raw('MAX(amount) as max_amount'))
              ->groupBy('employee_id')
              ->get();

Like the minimum example, you will get a collection with the maximum sale amount for each employee.

Advanced Grouping and Aggregation

More complex scenarios can include multiple column groupings, filtering groups, or performing additional calculations. Let us consider such cases.

Grouping by Multiple Columns

Suppose we want to find the maximum sale amount made by each employee for each month:

$maxMonthlySales = Sale::select('employee_id', 'month', DB::raw('MAX(amount) as max_amount'))
                     ->groupBy('employee_id', 'month')
                     ->get();

In this query, we’ve added another grouping column ‘month’, which allows us to get results for each month and employee combination.

Filtering on Aggregated Values

You may want to filter groups based on the aggregated value, such as finding employees who have made sales over a certain amount:

$largeSales = Sale::select('employee_id', DB::raw('MAX(amount) as max_amount'))
                  ->groupBy('employee_id')
                  ->having('max_amount', '>', 5000)
                  ->get();

Here, ‘having’ is used in place of ‘where’ because we’re dealing with a grouped value.

Advanced Eloquent and Query Builder Techniques

In some situations, you may require information that is beyond simple aggregation and requires joining multiple tables or using subqueries:

Using Joins with GroupBy

To include additional context to our sales data, possibly from a related ’employees’ table:

$employeeSales = Sale::select('employees.name', DB::raw('MAX(sales.amount) as max_amount'))
                    ->join('employees', 'sales.employee_id', '=', 'employees.id')
                    ->groupBy('employees.name')
                    ->get();

This code will give you the maximum sale amount made, alongside the names of the employees.

Grouping within Subqueries

If your grouping criteria are complex, you may need to use a subquery to temporary tables for your grouping:

$monthlySales = Sale::select('employee_id', 'month', DB::raw('SUM(amount) as total_sales'))
                   ->groupBy('employee_id', 'month')
                   ->orderBy('total_sales', 'desc')
                   ->get();

$topSalesByMonth = DB::table(DB::raw('('. $monthlySales->toSql() .') as sub'))
                   ->mergeBindings($monthlySales->getQuery())
                   ->select('month', DB::raw('MAX(total_sales) as max_sales'))
                   ->groupBy('month')
                   ->get();

In this example, we first create a subquery to get total sales per employee per month, then derive the maximum of these total sales across all employees for each month.

Utilizing Eloquent Resources

When returning results from the database, you could use Eloquent Resources to transform and format the data to correspondent to API specifications, or just to present it in a neater format.

Conclusion

This tutorial explored how to use Laravel’s Eloquent ORM to find the MIN/MAX values in groups. Whether you’re generating reports or performing complex data analysis, these techniques can be intrinsic parts of your toolset. With a little creativity, you can build upon these foundations to meet a wide array of business requirements.