Use GroupBy with Eloquent ORM in Laravel Framework

Create new database named laravel_db. In this database, create 2 tables named category and product as below:

Download and install the latest version of Composer from https://getcomposer.org/download/
Open Windows Terminal in Visual Studio Code install Laravel Installer with command as below:
```
composer global require laravel/installer
```
Create new folder named LearnLaravelWithRealApps. Use Visual Studio Code open to LearnLaravelWithRealApps foler. In this folder, create new project named LearnLaravelWithRealApps with command as below:
```
laravel new LearnLaravelWithRealApps
```
Run LearnLaravelWithRealApps project with command as below:
```
php artisan serve
```
Open LearnLaravelWithRealApps project with url as below:
```
http://localhost:8000
```

Open .env file in root folder. Add values as below connect to database:

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=laravel_db
DB_USERNAME=root
DB_PASSWORD=

Open Windows Terminal in Visual Studio Code and clear config cache with command as below:

php artisan config:clear

In app/Models folder, create new models as below:

Create new PHP file named Category.php in app/Models folder as below:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    protected $primarykey = 'id';
    protected $table = 'category';
    public $timestamps = false;

    public function products()
    {
        return $this->hasMany(Product::class);
    } 
}

Create new PHP file named Product.php in app/Models folder as below:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    public $primarykey = 'id';
    protected $table = 'product';
    public $timestamps = false;

    public function category()
    {
        return $this->belongsTo(Category::class, 'category_id');
    }
}

Create new folder named Repositories. In Repositories folder, create new folder named Product. Create new PHP file named ProductRepository.php in app/Repositories/Product folder as below:

<?php

namespace App\Repositories\Category;

interface CategoryRepository
{
    public function groupBy();
}

Create new PHP file named CategoryRepositoryImpl.php in app/Repositories/Category folder as below:

<?php

namespace App\Repositories\Product;

use App\Models\Product;
use Illuminate\Support\Facades\DB;

class ProductRepositoryImpl implements ProductRepository
{
    public function groupBy()
    {
        return Product::select("category_id", DB::raw("count(*) as countProduct"), DB::raw("sum(quantity) as sumQuantity"), DB::raw("min(price) as minPrice"), DB::raw("max(price) as maxPrice"), DB::raw("avg(price) as avgPrice"))->groupBy('category_id')->get();
    }
}

Open AppServiceProvider.php file in app/Providers folder and add declare new repository to register method as below:

<?php

namespace App\Providers;

use App\Repositories\Category\CategoryRepository;
use App\Repositories\Category\CategoryRepositoryImpl;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function register()
    {
        $this->app->bind(CategoryRepository::class, CategoryRepositoryImpl::class);
    }

    public function boot()
    {
    }
}

Create new PHP file named DemoController.php in app/Http/Controllers folder as below:

<?php

namespace App\Http\Controllers;

use App\Repositories\Category\CategoryRepository;

class DemoController extends Controller
{
    protected $categoryRepository;

    public function __construct(CategoryRepository $categoryRepository)
    {
        $this->categoryRepository = $categoryRepository;
    }

    public function index()
    {
        $data = array(
            'groups' => $this->productRepository->groupBy()
        );
        return view('demo/index')->with($data);
    }
}

Create new folder named demo in resources/views folder. In this folder, create new Blade file named index.blade.php as below:

<html>

        <head>
        <title>Laravel</title>
    </head>

    <body>

        <h3>Index</h3>
        <table border="1">
            <tr>
                <th>Category Id</th>
                <th>Count Product</th>
                <th>Sum Quantity</th>
                <th>Min Price</th>
                <th>Max Price</th>
                <th>Avg Price</th>
            </tr>
            @foreach($groups as $group)
            <tr>
                <td>{{$group->category_id}}</td>
                <td>{{$group->countProduct}}</td>
                <td>{{$group->sumQuantity}}</td>
                <td>{{$group->minPrice}}</td>
                <td>{{$group->maxPrice}}</td>
                <td>{{$group->avgPrice}}</td>
            </tr>
            @endforeach
        </table>

    </body>

</html>

Open web.php file in routes folder, add new routes as below:

<?php
    
use Illuminate\Support\Facades\Route;

use App\Http\Controllers\DemoController;

Route::get('/', [DemoController::class, 'index']);
Route::get('/demo', [DemoController::class, 'index']);
Route::get('/demo/index', [DemoController::class, 'index']);

Access index action in Demo controller with urls as below:

http://localhost:8000
http://localhost:8000/demo
http://localhost:8000/demo/index

Output

Create Database

Structure of Category Table

Data of Category Table

Structure of Product Table

Data of Product Table

Database Relationship Diagrams

Install Laravel

Connect to Database

Clear Config Cache

Create Models

Create Category Model

Create Product Model

Create ProductRepository Interface

Create CategoryRepositoryImpl Class

Declare Category Repository

Controllers

Views

Routes

Structure of Laravel Project

Run Application