Database

Framework cung cấp ORM và Query Builder mạnh mẽ với Connection Pooling cho hiệu năng cao.

Cấu hình

// config/database.php
return [
    'default' => 'mysql',

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', 3306),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'pool' => [
                'min_connections' => 1,
                'max_connections' => 10,
            ],
        ],
    ],
];

Query Builder

Select Queries

use Vietiso\Core\Database\DB;

// Lấy tất cả records
$users = DB::table('users')->get();

// Lấy record đầu tiên
$user = DB::table('users')->first();

// Lấy theo ID
$user = DB::table('users')->find(1);

// Select columns cụ thể
$users = DB::table('users')
    ->select('id', 'name', 'email')
    ->get();

// Alias
$users = DB::table('users')
    ->select('id', 'name as full_name')
    ->get();

Where Clauses

// Where cơ bản (column, value, operator)
// Mặc định operator là '='
$users = DB::table('users')
    ->where('status', 'active')
    ->get();

// Với operator (tham số thứ 3)
$users = DB::table('users')
    ->where('age', 18, '>=')
    ->get();

// Multiple conditions
$users = DB::table('users')
    ->where('status', 'active')
    ->where('role', 'admin')
    ->get();

// Or Where
$users = DB::table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'moderator')
    ->get();

// Where In
$users = DB::table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();

// Where Not In
$users = DB::table('users')
    ->whereNotIn('status', ['banned', 'inactive'])
    ->get();

// Where Null / Not Null
$users = DB::table('users')
    ->whereNull('deleted_at')
    ->get();

$users = DB::table('users')
    ->whereNotNull('email_verified_at')
    ->get();

// Where Between
$orders = DB::table('orders')
    ->whereBetween('created_at', ['2024-01-01', '2024-12-31'])
    ->get();

// Where Like
$users = DB::table('users')
    ->where('name', '%john%', 'like')
    ->get();

// Where Contains (shorthand for LIKE %value%)
$users = DB::table('users')
    ->whereContains('name', 'john')
    ->get();

// Where với array conditions
$users = DB::table('users')
    ->where([
        'status' => 'active',
        'role' => 'admin',
    ])
    ->get();

// Where Any - match bất kỳ column nào
$users = DB::table('users')
    ->whereAny(['name', 'email'], '%john%', 'like')
    ->get();

// Where All - match tất cả columns
$users = DB::table('users')
    ->whereAll(['status', 'role'], 'active')
    ->get();

Where Date/Time

// Where Date
$orders = DB::table('orders')
    ->whereDate('created_at', '2024-01-15')
    ->get();

// Với operator
$orders = DB::table('orders')
    ->whereDate('created_at', '2024-01-15', '>=')
    ->get();

// Where Month
$orders = DB::table('orders')
    ->whereMonth('created_at', '01')
    ->get();

// Where Year
$orders = DB::table('orders')
    ->whereYear('created_at', '2024')
    ->get();

// Where Day
$orders = DB::table('orders')
    ->whereDay('created_at', '15')
    ->get();

// Where Time
$orders = DB::table('orders')
    ->whereTime('created_at', '10:00:00', '>=')
    ->get();

Where Column

// So sánh 2 columns
$users = DB::table('users')
    ->whereColumn('updated_at', 'created_at', '>')
    ->get();

// Với array
$users = DB::table('users')
    ->whereColumn([
        ['updated_at', '>', 'created_at'],
        ['name', 'username'],
    ])
    ->get();

Subqueries

// Where Exists
$users = DB::table('users')
    ->whereExists(function ($query) {
        $query->table('orders')
            ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

// Where In với subquery
$users = DB::table('users')
    ->whereIn('id', function ($query) {
        $query->table('orders')
            ->select('user_id')
            ->where('total', 1000, '>');
    })
    ->get();

Ordering & Limiting

// Order By
$users = DB::table('users')
    ->orderBy('created_at', 'desc')
    ->get();

// Order By Desc shorthand
$users = DB::table('users')
    ->orderByDesc('created_at')
    ->get();

// Latest / Oldest
$users = DB::table('users')->latest()->get();     // ORDER BY created_at DESC
$users = DB::table('users')->oldest()->get();     // ORDER BY created_at ASC
$users = DB::table('users')->latest('updated_at')->get();

// Limit & Offset
$users = DB::table('users')
    ->limit(10)
    ->offset(20)
    ->get();

// Hoặc
$users = DB::table('users')
    ->limit(10, 20)  // limit, offset
    ->get();

Aggregates

// Count
$count = DB::table('users')->count();

// Max/Min
$maxPrice = DB::table('products')->max('price');
$minPrice = DB::table('products')->min('price');

// Sum/Avg
$total = DB::table('orders')->sum('amount');
$average = DB::table('products')->avg('price');

// Exists
if (DB::table('users')->where('email', $email)->exists()) {
    // User tồn tại
}

// Doesn't Exist
if (DB::table('users')->where('email', $email)->doesntExist()) {
    // User không tồn tại
}

Joins

// Inner Join (column1, column2, operator)
$users = DB::table('users')
    ->join('orders', 'users.id', 'orders.user_id')
    ->select('users.*', 'orders.total')
    ->get();

// Với operator khác
$users = DB::table('users')
    ->join('orders', 'users.id', 'orders.user_id', '=')
    ->get();

// Left Join
$users = DB::table('users')
    ->leftJoin('profiles', 'users.id', 'profiles.user_id')
    ->get();

// Right Join
$users = DB::table('users')
    ->rightJoin('orders', 'users.id', 'orders.user_id')
    ->get();

// Join với callback (advanced conditions)
$users = DB::table('users')
    ->join('orders', function ($join) {
        $join->on('users.id', 'orders.user_id')
            ->where('orders.status', 'completed');
    })
    ->get();

// Join Sub Query
$latestOrders = DB::table('orders')
    ->select('user_id', DB::raw('MAX(created_at) as last_order'))
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestOrders, 'latest_orders', function ($join) {
        $join->on('users.id', 'latest_orders.user_id');
    })
    ->get();

Group By & Having

// Group By
$orders = DB::table('orders')
    ->select('user_id')
    ->addSelectRaw('SUM(total) as total_amount')
    ->groupBy('user_id')
    ->get();

// Having (column, value, operator)
$orders = DB::table('orders')
    ->select('user_id')
    ->addSelectRaw('SUM(total) as total_amount')
    ->groupBy('user_id')
    ->having('total_amount', 1000, '>')
    ->get();

// Having Raw
$orders = DB::table('orders')
    ->selectRaw('user_id, SUM(total) as total')
    ->groupBy('user_id')
    ->havingRaw('SUM(total) > ?', [1000])
    ->get();

Insert

// Insert single record
DB::table('users')->insert([
    'name' => 'John',
    'email' => 'john@example.com',
]);

// Insert và lấy ID
$id = DB::table('users')->insertGetId([
    'name' => 'John',
    'email' => 'john@example.com',
]);

// Insert nhiều records
DB::table('users')->insert([
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com'],
]);

// Insert or Ignore
DB::table('users')->insertOrIgnore([
    'email' => 'john@example.com',
    'name' => 'John',
]);

// Update or Insert
DB::table('users')->updateOrInsert(
    ['email' => 'john@example.com'],  // conditions
    ['name' => 'John Doe']            // values to update/insert
);

Update

// Update với where
DB::table('users')
    ->where('id', 1)
    ->update(['name' => 'John Doe']);

// Increment
DB::table('products')
    ->where('id', 1)
    ->increment('stock', 5);

// Increment với additional fields
DB::table('products')
    ->where('id', 1)
    ->increment('stock', 5, ['updated_at' => now()]);

// Decrement
DB::table('products')
    ->where('id', 1)
    ->decrement('stock', 2);

// Increment/Decrement nhiều columns
DB::table('products')
    ->where('id', 1)
    ->incrementEach([
        'views' => 1,
        'downloads' => 2,
    ]);

Delete

// Delete với where
DB::table('users')
    ->where('id', 1)
    ->delete();

// Truncate
DB::table('logs')->truncate();

Transactions

use Vietiso\Core\Database\DB;

// Transaction với callback
$result = DB::query()->transaction(function () {
    DB::table('users')->insert([...]);
    DB::table('profiles')->insert([...]);
    return true;
});

// Manual transaction
DB::query()->beginTransaction();
try {
    DB::table('users')->insert([...]);
    DB::table('profiles')->insert([...]);
    DB::query()->commit();
} catch (\Exception $e) {
    DB::query()->rollBack();
    throw $e;
}

Raw Queries

// Select Raw
$users = DB::table('users')
    ->selectRaw('COUNT(*) as total, status')
    ->groupBy('status')
    ->get();

// Add Select Raw
$users = DB::table('users')
    ->select('name')
    ->addSelectRaw('(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as orders_count')
    ->get();

// Where Raw
$users = DB::table('users')
    ->whereRaw('YEAR(created_at) = ?', [2024])
    ->get();

// Order By Raw
$users = DB::table('users')
    ->orderByRaw('FIELD(status, "active", "pending", "inactive")')
    ->get();

Pagination

// Pagination cơ bản
$users = DB::table('users')->paginate(15);

// Với page cụ thể
$users = DB::table('users')->paginate(15, page: 2);

// Fast Paginate (hiệu quả hơn với dữ liệu lớn)
$users = DB::table('users')->fastPaginate(15);

Cursor (Memory Efficient)

// Xử lý từng record một - tiết kiệm memory
foreach (DB::table('users')->cursor() as $user) {
    // Process $user
}

Locking

// Lock for update
$user = DB::table('users')
    ->where('id', 1)
    ->lockForUpdate()
    ->first();

// Shared lock
$user = DB::table('users')
    ->where('id', 1)
    ->sharedLock()
    ->first();

Debug

// Xem SQL query
$sql = DB::table('users')
    ->where('status', 'active')
    ->toSql();

// Xem SQL với bindings
$rawSql = DB::table('users')
    ->where('status', 'active')
    ->toRawSql();

// Pretty print
$rawSql = DB::table('users')
    ->where('status', 'active')
    ->toRawSql(pretty: true);

// Explain query
$explain = DB::table('users')
    ->where('status', 'active')
    ->explain();