Laravel Query Builder Cheat Sheet

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.