How to Write a Raw SQL Query in Laravel

Problem: You need to run a complex SQL query that's difficult to express with Eloquent or Query Builder, or you need to use database-specific features.

Solution:

use Illuminate\Support\Facades\DB;

// SELECT queries - returns array of stdClass objects
$users = DB::select('SELECT * FROM users WHERE votes > ?', [100]);
$users = DB::select('SELECT * FROM users WHERE votes > :votes', ['votes' => 100]);

// INSERT, UPDATE, DELETE - returns affected rows count
$affected = DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['John', 'john@example.com']);
$affected = DB::update('UPDATE users SET votes = 100 WHERE name = ?', ['John']);
$affected = DB::delete('DELETE FROM users WHERE votes < ?', [100]);

// General statements (DDL, etc) - returns boolean
DB::statement('DROP TABLE users');
DB::statement('ALTER TABLE users ADD COLUMN age INT');

// Using DB::raw() in Query Builder
$users = User::select(DB::raw('COUNT(*) as user_count, status'))
    ->where('status', '!=', 'deleted')
    ->groupBy('status')
    ->get();

// Complex raw query with joins
$results = DB::select(DB::raw("
    SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.created_at > :date
    GROUP BY u.id, u.name
    HAVING order_count > :min_orders
"), ['date' => '2024-01-01', 'min_orders' => 5]);

Why it works: DB::select() runs SELECT queries and returns results. DB::insert(), update(), and delete() return affected row counts. DB::statement() runs any SQL statement. Parameter binding (? or :name) prevents SQL injection.

Warning: Always use parameter binding for user input. Never concatenate variables directly into SQL strings to avoid SQL injection vulnerabilities.

Related Topics

• Practical CQRS in Laravel Without Overengineering - Organize complex queries with CQRS