whereRaw() and DB::raw() — when and how to use safely
Concept
whereRaw() and DB::raw() allow injecting raw SQL fragments into the query. This is necessary for SQL features the Query Builder doesn't abstract (complex expressions, database-specific functions, window functions).
whereRaw(string $sql, array $bindings = []): Add a raw SQL WHERE condition with PDO parameter bindings.
DB::raw(string $expression): Wraps a string in a Expression object that the Query Builder will emit verbatim. Can be used in select(), orderBy(), groupBy(), where(), etc.
Security: ALWAYS use parameter bindings (? or named params) for user-controlled values in raw SQL. NEVER interpolate variables directly into raw SQL strings.
selectRaw(), whereRaw(), orderByRaw(), groupByRaw(), havingRaw(): Convenience methods that combine DB::raw() with the respective clause.
When to use raw:
- Date/time math:
DATE_ADD(created_at, INTERVAL 30 DAY). - Window functions:
ROW_NUMBER() OVER (PARTITION BY ...). - Full-text search:
MATCH(column) AGAINST(? IN BOOLEAN MODE). - Database-specific features not in the Query Builder.
PDO bindings in raw: Use ? for positional, :name for named. whereRaw('YEAR(created_at) = ?', [$year]). The bindings are PDO-escaped — safe against SQL injection.
Code Example
<?php
use Illuminate\Support\Facades\DB;
// whereRaw — raw SQL condition with bindings
$users = DB::table('users')
->whereRaw('DATEDIFF(NOW(), created_at) > ?', [30])
->get();
$results = DB::table('products')
->whereRaw('JSON_CONTAINS(tags, ?)', [json_encode('electronics')])
->get();
// Full-text search (MySQL)
$posts = DB::table('posts')
->whereRaw('MATCH(title, body) AGAINST(? IN BOOLEAN MODE)', [$searchTerm])
->get();
// Date math
$expiredSessions = DB::table('sessions')
->whereRaw('last_activity < DATE_SUB(NOW(), INTERVAL ? SECOND)', [config('session.lifetime') * 60])
->get();
// selectRaw — computed columns
$stats = DB::table('orders')
->selectRaw('
user_id,
COUNT(*) as order_count,
SUM(total) as lifetime_value,
MAX(created_at) as last_order_at,
MIN(total) as smallest_order
')
->groupBy('user_id')
->get();
// orderByRaw
$users = DB::table('users')
->orderByRaw('FIELD(role, "admin", "editor", "user")') // custom sort order
->get();
// groupByRaw
$dailyRevenue = DB::table('orders')
->selectRaw('DATE(created_at) as day, SUM(total) as revenue')
->groupByRaw('DATE(created_at)')
->get();
// Window function (MySQL 8+, PostgreSQL)
$ranked = DB::table('orders')
->selectRaw('
user_id, total,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
')
->get()
->where('rn', 1); // most recent order per user
// NEVER do this — SQL injection risk!
// $column = $request->input('sort'); // user-controlled!
// DB::table('users')->whereRaw("name = '$column'"); // DANGEROUS
// SAFE — use bindings
$value = $request->input('search');
DB::table('users')->whereRaw('name = ?', [$value]);