Raw Query

Execute raw SQL queries when you need full control over your database operations.

Raw Query Methods

Basic Usage

SELECT Queries

use Miko\Database\DB;

// Simple query
$users = DB::query("SELECT * FROM users");

// With parameters (prepared statement)
$users = DB::query(
    "SELECT * FROM users WHERE IsActive = ? AND Role = ?",
    [true, 'admin']
);

// Named parameters
$users = DB::query(
    "SELECT * FROM users WHERE Email = :email",
    ['email' => 'john@example.com']
);

INSERT

$affected = DB::execute(
    "INSERT INTO users (Name, Email, Password) VALUES (?, ?, ?)",
    ['John Doe', 'john@example.com', password_hash('secret', PASSWORD_DEFAULT)]
);

// Get last insert ID
$id = DB::lastInsertId();

UPDATE

$affected = DB::execute(
    "UPDATE users SET IsActive = ? WHERE Id = ?",
    [true, 1]
);

echo "Updated $affected rows";

DELETE

$affected = DB::execute(
    "DELETE FROM sessions WHERE ExpiredAt < ?",
    [date('Y-m-d H:i:s')]
);

Scalar Values

Get a single value from query:

// Count
$count = DB::scalar("SELECT COUNT(*) FROM users WHERE IsActive = ?", [true]);

// Sum
$total = DB::scalar("SELECT SUM(TotalAmount) FROM orders WHERE Status = ?", ['completed']);

// Single value
$name = DB::scalar("SELECT Name FROM users WHERE Id = ?", [1]);

First Row

Get first matching row:

$user = DB::first("SELECT * FROM users WHERE Email = ?", ['john@example.com']);

if ($user) {
    echo $user['Name'];
}

Using with Model

// Raw query returning models
$users = User::fromQuery(
    "SELECT * FROM users WHERE YEAR(CreatedDate) = ?",
    [2024]
);

// whereRaw in query builder
$users = User::whereRaw("Age > ? AND Role IN (?, ?)", [18, 'admin', 'moderator'])->get();

// selectRaw
$stats = User::selectRaw("Role, COUNT(*) as Count, AVG(Age) as AvgAge")
    ->groupBy('Role')
    ->get();

// orderByRaw
$users = User::orderByRaw("FIELD(Status, 'active', 'pending', 'inactive')")->get();

Complex Queries

Joins

$results = DB::query("
    SELECT 
        u.Name,
        u.Email,
        COUNT(o.Id) as OrderCount,
        SUM(o.TotalAmount) as TotalSpent
    FROM users u
    LEFT JOIN orders o ON u.Id = o.UserId
    WHERE u.IsActive = ?
    GROUP BY u.Id
    HAVING OrderCount > ?
    ORDER BY TotalSpent DESC
    LIMIT 10
", [true, 5]);

Subqueries

$users = DB::query("
    SELECT * FROM users
    WHERE Id IN (
        SELECT UserId FROM orders
        WHERE TotalAmount > ?
        GROUP BY UserId
        HAVING COUNT(*) > ?
    )
", [1000, 3]);

UNION

$results = DB::query("
    SELECT Id, Name, 'user' as Type FROM users WHERE IsActive = 1
    UNION ALL
    SELECT Id, Name, 'admin' as Type FROM admins WHERE IsActive = 1
    ORDER BY Name
");

Transactions with Raw Queries

DB::beginTransaction();

try {
    DB::execute("INSERT INTO orders (UserId, TotalAmount) VALUES (?, ?)", [1, 100]);
    $orderId = DB::lastInsertId();
    
    DB::execute("INSERT INTO order_items (OrderId, ProductId, Quantity) VALUES (?, ?, ?)", 
        [$orderId, 5, 2]);
    
    DB::execute("UPDATE products SET Stock = Stock - ? WHERE Id = ?", [2, 5]);
    
    DB::commit();
} catch (Exception $e) {
    DB::rollback();
    throw $e;
}

Database-Specific Queries

MySQL

// Full-text search
$results = DB::query("
    SELECT * FROM articles
    WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
", ['+php +mysql']);

// JSON queries
$users = DB::query("
    SELECT * FROM users
    WHERE JSON_EXTRACT(settings, '$.theme') = ?
", ['dark']);

PostgreSQL

// Array contains
$users = DB::query("SELECT * FROM users WHERE ? = ANY(tags)", ['admin']);

// JSONB
$users = DB::query("SELECT * FROM users WHERE settings->>'theme' = ?", ['dark']);

Security Best Practices

// GOOD: Using prepared statements
$users = DB::query("SELECT * FROM users WHERE Email = ?", [$email]);

// BAD: String concatenation (SQL injection risk!)
// $users = DB::query("SELECT * FROM users WHERE Email = '$email'");

// GOOD: Escaping identifiers
$column = 'Name';
$users = DB::query("SELECT `$column` FROM users");