Integrate Raw SQL Expressions in Eloquent Queries

<?php

namespace App\Http\Controllers;

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

class ReportController extends Controller
{
    public function monthlySales()
    {
        // 1. Select the month, year, and sum of sales using raw SQL for aggregation
        $monthlySales = DB::table('orders')
            ->select(DB::raw('YEAR(created_at) as year, MONTH(created_at) as month, SUM(total_amount) as total_sales'))
            ->groupBy(DB::raw('YEAR(created_at), MONTH(created_at)'))
            ->orderBy(DB::raw('year DESC, month DESC'))
            ->get();

        // 2. Filter products where a specific column's value matches a regex pattern (PostgreSQL example)
        $productsMatchingPattern = Product::whereRaw("name ~* '^[A-Z].*(premium|deluxe)$'")->get();

        // 3. Select products and calculate a derived attribute using a raw expression
        $productsWithDiscountedPrice = Product::select('name', 'price', DB::raw('price * 0.9 AS discounted_price'))
            ->get();

        // 4. Order by a more complex expression (e.g., case statement or function call)
        $orderedProducts = Product::orderByRaw('LENGTH(name) DESC, price ASC')->get();

        // 5. Using raw expressions in a join clause
        // DB::table('users')
        //     ->join('posts', function ($join) {
        //         $join->on('users.id', '=', 'posts.user_id')
        //              ->whereRaw('users.created_at < posts.created_at');
        //     })
        //     ->get();

        return view('reports.sales', compact('monthlySales', 'productsMatchingPattern', 'productsWithDiscountedPrice', 'orderedProducts'));
    }
}