This cheat sheet summarizes the basic and frequently used features of Laravel’s Query Builder. Ideal for beginners and intermediate developers looking for a quick reference.
🏁 Basic Queries
Get All Records
$users = DB::table('users')->get();
Get First Record
$user = DB::table('users')->first();
Where Clause
$users = DB::table('users')
->where('active', 1)
->get();
OR Where
$users = DB::table('users')
->where('role', 'admin')
->orWhere('role', 'editor')
->get();
Select Specific Columns
$names = DB::table('users')
->select('id', 'name')
->get();
Order By
$users = DB::table('users')
->orderBy('created_at', 'desc')
->get();
Limit / Offset
$users = DB::table('users')
->limit(10)
->offset(20)
->get();
🔄 Insert / Update / Delete
Insert
DB::table('users')->insert([
'name' => 'John Doe',
'email' => 'john@example.com'
]);
Insert and Get ID
$id = DB::table('users')->insertGetId([
'name' => 'Jane Doe',
'email' => 'jane@example.com'
]);
Update
DB::table('users')
->where('id', 1)
->update(['name' => 'Updated Name']);
Delete
DB::table('users')
->where('id', 1)
->delete();
📊 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');
🧩 Join Queries
Inner Join
$users = DB::table('users')
->join('posts', 'users.id', '=', 'posts.user_id')
->select('users.*', 'posts.title')
->get();
Left Join
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
🔐 Transactions
DB::transaction(function () {
DB::table('users')->update(['active' => false]);
DB::table('logs')->insert(['message' => 'Users deactivated']);
});
💡 Useful Tips
Raw Expressions
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->groupBy('status')
->get();
Exists
$exists = DB::table('users')
->where('email', 'john@example.com')
->exists();
Doesn’t Exist
$missing = DB::table('users')
->where('email', 'john@example.com')
->doesntExist();
When (conditional query building)
$role = 'admin';
$users = DB::table('users')
->when($role, function ($query, $role) {
return $query->where('role', $role);
})
->get();
📚 Summary
Laravel’s Query Builder is a powerful, flexible, and fluent interface for building SQL queries. It helps keep your code readable and secure while offering excellent database abstraction.
For deeper insights, check the Laravel documentation.
コメント