Blog

Laravel Group By with Max Value Query Example
Laravel Group By with Max Value Query Example

Laravel Group By with Max Value Query Example

In this article, we’ll explore how to use Laravel’s powerful Eloquent ORM to group records and find the maximum value within those groups. This is a common requirement in many applications, such as finding the highest salary in each department, the most expensive product in each category, etc. Let’s dive in with a step-by-step example that’s easy to understand.

Step-by-Step Guide

1. Setting Up Laravel Project

First, make sure you have a Laravel project set up. If not, you can create one using Composer:

composer create-project --prefer-dist laravel/laravel group-by-example

Navigate into your project directory:

cd group-by-example
2. Create a Database and Configure .env File

Set up your database and update the .env file with your database credentials:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database_name
DB_USERNAME=your_database_user
DB_PASSWORD=your_database_password
3. Create a Migration and Model

Let’s create a migration and model for our example. We’ll use a Product model to demonstrate grouping by category and finding the most expensive product in each category.

Run the following Artisan command:

php artisan make:model Product -m

This command creates a Product model and a migration file. Open the migration file located in database/migrations and update it like so:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateProductsTable extends Migration
{
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('category');
            $table->decimal('price', 8, 2);
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('products');
    }
}

Run the migration to create the table:

php artisan migrate
4. Seed the Database with Sample Data

Create a seeder to populate the products table with sample data. Run:

php artisan make:seeder ProductSeeder

Edit the ProductSeeder located in database/seeders/ProductSeeder.php:

use Illuminate\Database\Seeder;
use App\Models\Product;

class ProductSeeder extends Seeder
{
    public function run()
    {
        Product::truncate();

        $products = [
            ['name' => 'Product 1', 'category' => 'Electronics', 'price' => 99.99],
            ['name' => 'Product 2', 'category' => 'Electronics', 'price' => 199.99],
            ['name' => 'Product 3', 'category' => 'Furniture', 'price' => 299.99],
            ['name' => 'Product 4', 'category' => 'Furniture', 'price' => 399.99],
            ['name' => 'Product 5', 'category' => 'Clothing', 'price' => 49.99],
            ['name' => 'Product 6', 'category' => 'Clothing', 'price' => 59.99],
        ];

        foreach ($products as $product) {
            Product::create($product);
        }
    }
}

Run the seeder:

php artisan db:seed --class=ProductSeeder
5. Write the Group By Query

Now, let’s write the query to group products by category and get the most expensive product in each category. We will use Laravel’s query builder for this.

Create a controller to handle the logic. Run:

php artisan make:controller ProductController

Edit the ProductController located in app/Http/Controllers/ProductController.php:

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Product;
use DB;

class ProductController extends Controller
{
    public function index()
    {
        $subQuery = Product::select('category', DB::raw('MAX(price) as max_price'))
            ->groupBy('category');

        $products = DB::table('products as p')
            ->joinSub($subQuery, 'sub', function ($join) {
                $join->on('p.category', '=', 'sub.category')
                     ->on('p.price', '=', 'sub.max_price');
            })
            ->select('p.*')
            ->get();

        return view('products.index', compact('products'));
    }
}
6. Create a View

Create a view to display the products. Make a new file at resources/views/products/index.blade.php:

<!DOCTYPE html>
<html>
<head>
    <title>Product List</title>
</head>
<body>
    <h1>Most Expensive Products in Each Category</h1>
    <table border="1">
        <thead>
            <tr>
                <th>Name</th>
                <th>Category</th>
                <th>Price</th>
            </tr>
        </thead>
        <tbody>
            @foreach ($products as $product)
            <tr>
                <td>{{ $product->name }}</td>
                <td>{{ $product->category }}</td>
                <td>${{ $product->price }}</td>
            </tr>
            @endforeach
        </tbody>
    </table>
</body>
</html>
7. Define the Route

Add a route to display the products in routes/web.php:

use App\Http\Controllers\ProductController;

Route::get('/products', [ProductController::class, 'index']);
8. View the Result

Start your Laravel development server:

php artisan serve

Visit http://localhost:8000/products in your browser. You should see a list of the most expensive products in each category.

Conclusion

In this article, we demonstrated how to use Laravel’s Eloquent ORM to group records by a specific field and find the maximum value within those groups. This technique is useful for many real-world applications. By following these steps, you can easily implement similar functionality in your Laravel projects.

Tags: