Query Builder

Giới thiệu (Introduction)

Database query builder cung cấp interface tiện lợi, fluent để tạo và chạy database queries. Sử dụng PDO parameter binding để bảo vệ chống SQL injection. Hoạt động với tất cả database systems mà Laravel hỗ trợ.

Chạy Database Queries

Lấy tất cả Rows

use Illuminate\Support\Facades\DB;

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

foreach ($users as $user) {
    echo $user->name;
}

Lấy một Row / Value

$user = DB::table('users')->where('name', 'John')->first();
echo $user->email;

$email = DB::table('users')->where('name', 'John')->value('email');

$user = DB::table('users')->find(3); // Tìm theo id

Lấy danh sách giá trị một cột

$titles = DB::table('posts')->pluck('title');

// Key-value pairs
$roles = DB::table('roles')->pluck('title', 'name');

Chunking Results

Xử lý nhiều records theo batch:

DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    foreach ($users as $user) {
        // Xử lý...
    }

    // return false; để dừng chunking
});

Lazy Streaming

DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // Xử lý từng user...
});

Aggregates

$count = DB::table('users')->count();
$max   = DB::table('orders')->max('price');
$min   = DB::table('orders')->min('price');
$avg   = DB::table('orders')->avg('price');
$sum   = DB::table('orders')->sum('price');

$exists = DB::table('orders')->where('finalized', 1)->exists();
$doesntExist = DB::table('orders')->where('finalized', 1)->doesntExist();

Select Statements

$users = DB::table('users')
    ->select('name', 'email as user_email')
    ->get();

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

// Thêm column
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

Raw Expressions

$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();

// selectRaw
$orders = DB::table('orders')
    ->selectRaw('price * ? as price_with_tax', [1.0825])
    ->get();

// whereRaw / orWhereRaw
$orders = DB::table('orders')
    ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
    ->get();

// havingRaw
$orders = DB::table('orders')
    ->select('department', DB::raw('SUM(price) as total_sales'))
    ->groupBy('department')
    ->havingRaw('SUM(price) > ?', [2500])
    ->get();

// orderByRaw
$orders = DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')
    ->get();

Joins

// Inner Join
$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();

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

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

// Cross Join
$sizes = DB::table('sizes')
    ->crossJoin('colors')
    ->get();

// Advanced Join
DB::table('users')
    ->join('contacts', function (JoinClause $join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->orOn(/* ... */);
    })
    ->get();

// Subquery Join
$latestPosts = DB::table('posts')
    ->select('user_id', DB::raw('MAX(created_at) as last_post'))
    ->groupBy('user_id');

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

Unions

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')
    ->whereNull('last_name')
    ->union($first)
    ->get();

Where Clauses

Cơ bản

$users = DB::table('users')
    ->where('votes', '=', 100)
    ->where('age', '>', 35)
    ->get();

// Viết tắt (mặc định '=')
$users = DB::table('users')->where('votes', 100)->get();

// Nhiều điều kiện
$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Or Where

$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', 'John')
    ->get();

// Or Where với closure
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere(function (Builder $query) {
        $query->where('name', 'Abigail')
              ->where('votes', '>', 50);
    })
    ->get();
// WHERE votes > 100 OR (name = 'Abigail' AND votes > 50)

Where Not

$products = DB::table('products')
    ->whereNot(function (Builder $query) {
        $query->where('clearance', true)
              ->orWhere('price', '<', 10);
    })
    ->get();

Where Any / All / None

$users = DB::table('users')
    ->whereAny(['name', 'email'], 'like', '%Taylor%')
    ->get();
// WHERE (name LIKE '%Taylor%' OR email LIKE '%Taylor%')

$users = DB::table('users')
    ->whereAll(['name', 'email'], 'like', '%Taylor%')
    ->get();
// WHERE (name LIKE '%Taylor%' AND email LIKE '%Taylor%')

Additional Where Clauses

->whereBetween('votes', [1, 100])
->whereNotBetween('votes', [1, 100])
->whereIn('id', [1, 2, 3])
->whereNotIn('id', [1, 2, 3])
->whereNull('updated_at')
->whereNotNull('updated_at')
->whereDate('created_at', '2023-12-31')
->whereMonth('created_at', '12')
->whereDay('created_at', '31')
->whereYear('created_at', '2023')
->whereTime('created_at', '=', '11:20:45')
->whereColumn('first_name', 'last_name')
->whereColumn('updated_at', '>', 'created_at')

JSON Where Clauses

$users = DB::table('users')
    ->where('preferences->dining->meal', 'salad')
    ->get();

$users = DB::table('users')
    ->whereJsonContains('options->languages', 'en')
    ->get();

$users = DB::table('users')
    ->whereJsonLength('options->languages', '>', 1)
    ->get();

Logical Grouping

$users = DB::table('users')
    ->where('name', '=', 'John')
    ->where(function (Builder $query) {
        $query->where('votes', '>', 100)
              ->orWhere('title', '=', 'Admin');
    })
    ->get();
// WHERE name = 'John' AND (votes > 100 OR title = 'Admin')

Where Exists

$users = DB::table('users')
    ->whereExists(function (Builder $query) {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

Full Text Where

$users = DB::table('users')
    ->whereFullText('bio', 'web developer')
    ->get();

Ordering, Grouping, Limit & Offset

$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->get();

$users = DB::table('users')
    ->latest()        // ORDER BY created_at DESC
    ->first();

$randomUser = DB::table('users')->inRandomOrder()->first();

// Grouping
$users = DB::table('users')
    ->groupBy('account_id')
    ->having('account_id', '>', 100)
    ->get();

// Limit & Offset
$users = DB::table('users')->skip(10)->take(5)->get();
// Hoặc
$users = DB::table('users')->offset(10)->limit(5)->get();

Conditional Clauses

$users = DB::table('users')
    ->when($role, function (Builder $query, string $role) {
        $query->where('role_id', $role);
    })
    ->get();

// Với else
$users = DB::table('users')
    ->when($sortByVotes, function (Builder $query) {
        $query->orderBy('votes');
    }, function (Builder $query) {
        $query->orderBy('name');
    })
    ->get();

Insert

DB::table('users')->insert([
    'email' => 'kayla@example.com',
    'votes' => 0,
]);

// Insert nhiều records
DB::table('users')->insert([
    ['email' => 'picard@example.com', 'votes' => 0],
    ['email' => 'janeway@example.com', 'votes' => 0],
]);

// Auto-increment ID
$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

// Insert or Ignore (skip duplicates)
DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'sisko@example.com'],
]);

Upserts

DB::table('flights')->upsert(
    [
        ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
        ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150],
    ],
    ['departure', 'destination'],  // Unique columns
    ['price']                       // Columns to update on conflict
);

Update

$affected = DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);

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

JSON Columns

DB::table('users')
    ->where('id', 1)
    ->update(['options->enabled' => true]);

Increment / Decrement

DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);

// Kết hợp update
DB::table('users')->increment('votes', 1, ['name' => 'John']);

Delete

DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();
DB::table('users')->truncate(); // Xóa tất cả + reset auto-increment

Pessimistic Locking

// Shared lock (FOR SHARE)
DB::table('users')
    ->where('votes', '>', 100)
    ->sharedLock()
    ->get();

// Exclusive lock (FOR UPDATE)
DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();

Debugging

DB::table('users')->where('votes', '>', 100)->dd();
DB::table('users')->where('votes', '>', 100)->dump();
DB::table('users')->where('votes', '>', 100)->dumpRawSql();