PHP
Integrating Raw SQL Expressions into Laravel Eloquent Queries
Learn how to leverage `DB::raw()` within Eloquent queries to execute complex or database-specific SQL functions, aggregations, and conditions not directly supported by the fluent builder.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
class Order extends Model
{
// Example 1: Selecting a raw expression
// $orders = Order::select('id', 'customer_name', DB::raw('price * quantity AS total_amount'))
// ->get();
// foreach ($orders as $order) {
// echo $order->total_amount;
// }
// Example 2: Using raw expressions in WHERE clauses
// Find orders placed within the last 7 days
// $recentOrders = Order::where(DB::raw('DATE(created_at)'), '>=', DB::raw('DATE_SUB(CURDATE(), INTERVAL 7 DAY)'))
// ->get();
// Example 3: Using raw expressions for GROUP BY and HAVING
// Find customers with more than 5 orders, showing their total order count
// $customers = Order::select('customer_id', DB::raw('COUNT(id) as order_count'))
// ->groupBy('customer_id')
// ->having(DB::raw('COUNT(id)'), '>', 5)
// ->get();
}
How it works: While Eloquent's fluent query builder covers most common database operations, sometimes you need to use database-specific functions or complex SQL logic. `DB::raw()` allows you to inject raw SQL expressions directly into various parts of your Eloquent query, such as `select()`, `where()`, `groupBy()`, or `having()`. This offers flexibility for advanced queries while still benefiting from Eloquent's model-based approach. Be cautious with `DB::raw()` to avoid SQL injection by ensuring any user input is properly sanitized or handled by other Eloquent methods.