Skip to content

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 ​

php
use Illuminate\Support\Facades\DB;

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

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

Lấy một Row / Value ​

php
$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 ​

php
$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:

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

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

Lazy Streaming ​

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

Aggregates ​

php
$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 ​

php
$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 ​

php
$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 ​

php
// 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 ​

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

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

Where Clauses ​

Cơ bản ​

php
$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 ​

php
$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 ​

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

Where Any / All / None ​

php
$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 ​

php
->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 ​

php
$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 ​

php
$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 ​

php
$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 ​

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

Ordering, Grouping, Limit & Offset ​

php
$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 ​

php
$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 ​

php
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 ​

php
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 ​

php
$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 ​

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

Increment / Decrement ​

php
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 ​

php
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 ​

php
// 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 ​

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