Laravel Query Builder: A Complete Guide 🚀

Laravel's Query Builder provides a fluent, convenient interface to interact with databases without writing raw SQL queries. It works with various databases and supports CRUD operations, joins, aggregates, and more.

1. Getting Started with Query Builder

The DB facade (use Illuminate\Support\Facades\DB;) is used to run queries.

📄 Example: Import DB in a Controller

use Illuminate\Support\Facades\DB;

2. Retrieving Data (SELECT Queries)

Get All Records

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

Get a Single Record

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

Get a Specific Column

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

Select Specific Columns

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

Using WHERE Conditions

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

✅ Multiple WHERE Conditions

$users = DB::table('users')->where('age', '>', 25)->where('status', 'active')->get();

✅ WHERE OR Condition

$users = DB::table('users')->where('age', '>', 25)->orWhere('status', 'active')->get();

✅ WHERE IN Condition

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

✅ WHERE NOT IN Condition

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

3. Ordering, Grouping & Limiting Results

✅ Order By

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

✅ Group By & Having

$users = DB::table('users')
    ->select('role', DB::raw('COUNT(*) as total'))
    ->groupBy('role')
    ->having('total', '>', 5)
    ->get();

✅ Limit & Offset (Pagination)

$users = DB::table('users')->limit(10)->offset(5)->get();

4. Insert Data (CREATE)

✅ Insert Single Record

DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => bcrypt('password')
]);

✅ Insert Multiple Records

DB::table('users')->insert([
    ['name' => 'Alice', 'email' => 'alice@example.com', 'password' => bcrypt('password')],
    ['name' => 'Bob', 'email' => 'bob@example.com', 'password' => bcrypt('password')]
]);

✅ Insert & Get ID

$id = DB::table('users')->insertGetId([
    'name' => 'Charlie',
    'email' => 'charlie@example.com',
    'password' => bcrypt('password')
]);

5. Update Data (UPDATE)

✅ Update a Single Record

DB::table('users')->where('id', 1)->update(['name' => 'Updated Name']);

✅ Update Multiple Records

DB::table('users')->where('status', 'inactive')->update(['status' => 'active']);

✅ Increment & Decrement

DB::table('users')->where('id', 1)->increment('balance', 100); // Add 100
DB::table('users')->where('id', 1)->decrement('balance', 50);  // Subtract 50

6. Delete Data (DELETE)

✅ Delete a Record

DB::table('users')->where('id', 1)->delete();

✅ Delete Multiple Records

DB::table('users')->where('status', 'inactive')->delete();

✅ Truncate Table (Deletes All Data)

DB::table('users')->truncate();

7. Joins in Query Builder

Inner Join

$users = DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.name', 'orders.total')
    ->get();

Left Join

$users = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->get();

Right Join

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

8. Aggregates in Query Builder

✅ Counting Records

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

✅ Finding Maximum, Minimum, Sum, Average

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

9. Raw Queries in Query Builder

✅ Using Raw Expressions

$users = DB::table('users')
    ->select(DB::raw('COUNT(*) as total_users, status'))
    ->groupBy('status')
    ->get();

✅ Raw Where Condition

$users = DB::table('users')->whereRaw('age > ? AND status = ?', [25, 'active'])->get();

✅ Raw Insert

DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'john@example.com']);

✅ Raw Update

DB::update('UPDATE users SET status = ? WHERE id = ?', ['inactive', 1]);

✅ Raw Delete

DB::delete('DELETE FROM users WHERE id = ?', [1]);

Conclusion

✔ Laravel Query Builder simplifies database interactions.
✔ Supports CRUD operations, joins, aggregates, raw queries, and more.
✔ DB Facade makes it easy to execute SQL-like queries.

🚀 Want more Laravel tricks? Keep exploring! 😊