PHP
Integrating Raw SQL with Eloquent Queries
Explore how to incorporate raw SQL expressions and clauses directly into your Laravel Eloquent queries using `DB::raw()`, `whereRaw()`, and `selectRaw()` for advanced scenarios.
use Illuminate\Support\Facades\DB;
use App\Models\Post;
// 1. Using selectRaw() to add a raw SQL select statement
$postsWithCommentsCount = Post::selectRaw('posts.*, (SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id) as comments_count')
->orderBy('comments_count', 'desc')
->get();
// 2. Using whereRaw() for complex WHERE clauses
$recentPosts = Post::whereRaw('created_at > DATE_SUB(NOW(), INTERVAL ? DAY)', [7])
->get();
// 3. Using groupBy and havingRaw() with DB::raw()
$dailySales = DB::table('orders')
->select(
DB::raw('DATE(created_at) as order_date'),
DB::raw('SUM(total_amount) as total_sales')
)
->groupBy(DB::raw('DATE(created_at)'))
->havingRaw('SUM(total_amount) > ?', [1000])
->get();
// 4. Updating with raw expressions
DB::table('users')
->where('id', 1)
->update([
'last_login_at' => DB::raw('NOW()'),
'login_count' => DB::raw('login_count + 1')
]);
How it works: While Eloquent's fluent API covers most query needs, `DB::raw()` allows you to inject raw SQL into various parts of your query, like `selectRaw()`, `whereRaw()`, `havingRaw()`, and `orderByRaw()`. This is particularly useful for complex calculations, subqueries, or functions specific to your database that aren't directly supported by Eloquent's builder. Remember to use parameter binding with `whereRaw()` and `havingRaw()` to prevent SQL injection vulnerabilities.