OrderBy with GroupBy and Having with Eloquent ORM in Laravel Framework

Create Database

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

Structure of Category Table

Data of Category Table

Structure of Product Table

Data of Product Table

Database Relationship Diagrams

Install Laravel

1. Download and install the latest version of Composer from https://getcomposer.org/download/

2. Open Windows Terminal in Visual Studio Code install Laravel Installer with command as below:

   composer global require laravel/installer

3. 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

   

4. Run LearnLaravelWithRealApps project with command as below:

   php artisan serve

5. Open LearnLaravelWithRealApps project with url as below:

   http://localhost:8000

   

Connect to Database

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=

Clear Config Cache

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

php artisan config:clear

Create Models

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

Create Category Model

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 Product Model

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 ProductRepository Interface

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 groupByHavingOrderBy($sumQuantity);
}

Create CategoryRepositoryImpl Class

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 groupByHavingOrderBy($sumQuantity)
    {
        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')->havingRaw('sumQuantity > '.$sumQuantity)->orderBy('sumQuantity', 'ASC')->get();
    }
}

Declare Category Repository

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()
    {
    }
}

Controllers

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->groupByHavingOrderBy(17)
        );
        return view('demo/index')->with($data);
    }
}

Views

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>

Routes

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']);

Structure of Laravel Project

Run Application

Access index action in Demo controller with urls as below:

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

Output