Laravel’s Query Builder provides a powerful, fluent interface for building SQL queries in PHP. It allows you to interact with the database in an expressive, SQL-like syntax while abstracting away most of the complexity.

We’ll walk through a typical use case in a Laravel application using Query Builder for various tasks like selecting, inserting, updating, and deleting data.

Step 1: Setup Laravel Project

If you don’t have a Laravel project, you can set one up as follows:

composer create-project --prefer-dist laravel/laravel laravel-query-builder
cd laravel-query-builder
php artisan serve

Ensure you set up your database configuration in the .env file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

Run migrations for creating default tables:

php artisan migrate

Step 2: Use Query Builder in a Controller

Let’s create a controller to demonstrate the usage of Query Builder:

php artisan make:controller UserController

Edit UserController.php with the following code:

get();

        return response()->json($users);
    }

    // Insert a new user
    public function store(Request $request)
    {
        // Step 4: Insert a new user
        DB::table('users')->insert([
            'name' => $request->name,
            'email' => $request->email,
            'password' => bcrypt($request->password),
        ]);

        return response()->json(['message' => 'User created successfully!']);
    }

    // Update an existing user
    public function update(Request $request, $id)
    {
        // Step 5: Update user by ID
        DB::table('users')
            ->where('id', $id)
            ->update([
                'name' => $request->name,
                'email' => $request->email,
            ]);

        return response()->json(['message' => 'User updated successfully!']);
    }

    // Delete a user
    public function destroy($id)
    {
        // Step 6: Delete user by ID
        DB::table('users')->where('id', $id)->delete();

        return response()->json(['message' => 'User deleted successfully!']);
    }
}

Step 3: Retrieve Data

Use Query Builder to select all rows from the users table:

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

• Description:
  • The DB::table('users') method targets the users table.
  • The get() method retrieves all records from that table.

Example Response:

[
    {
        "id": 1,
        "name": "John Doe",
        "email": "[email protected]"
    },
    {
        "id": 2,
        "name": "Jane Doe",
        "email": "[email protected]"
    }
]

Step 4: Insert Data

Insert a new user using Query Builder:

DB::table('users')->insert([
    'name' => 'Alice',
    'email' => '[email protected]',
    'password' => bcrypt('password123'),
]);

• Description:
  • The insert() method inserts a new row into the users table.
  • The data is passed as an associative array where the keys match the column names.

This adds a new user to the users table.

Step 5: Update Data

To update an existing record, use update():

DB::table('users')
    ->where('id', 1)
    ->update([
        'name' => 'John Smith',
        'email' => '[email protected]'
    ]);

• Description:
  • The where() clause selects the row with id = 1.
  • The update() method modifies the name and email fields for the selected row.

This updates the user with ID 1 in the users table.

Step 6: Delete Data

To delete a record from the database, use delete():

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

• Description:
  • The where() clause specifies the condition to select the user with ID 2.
  • The delete() method removes the matching row from the table.

This deletes the user with ID 2.

Step 7: Filtering with Query Builder

You can chain additional methods to filter the data or add conditions to the query.

Example: Retrieve users with a specific condition

$users = DB::table('users')
    ->where('email', 'like', '%example.com%')
    ->orderBy('name', 'asc')
    ->get();

• Description:
  • The where() clause adds a condition, fetching users whose email contains example.com.
  • The orderBy() method sorts the users by name in ascending order.

Step 8: Pagination

Laravel’s Query Builder makes it easy to paginate results.

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

• Description:
  • The paginate() method breaks the results into pages, displaying 10 results per page.

Step 9: Transactions

Use database transactions to ensure that multiple queries are executed successfully. If one query fails, all changes are rolled back.

DB::transaction(function () {
    DB::table('users')->insert([
        'name' => 'John Doe',
        'email' => '[email protected]',
        'password' => bcrypt('password123')
    ]);

    DB::table('orders')->insert([
        'user_id' => 1,
        'order_total' => 500
    ]);
});

• Description:
  • The transaction() method ensures that both the users and orders table inserts are executed successfully. If either fails, both operations will be rolled back.

Step 10: Raw Queries

If you need to run raw SQL, Laravel’s Query Builder allows it:

$users = DB::select('SELECT * FROM users WHERE id = ?', [1]);

• Description:
  • The select() method can be used to execute raw SQL queries.
  • It uses prepared statements (?) for security, preventing SQL injection.

Conclusion

Laravel’s Query Builder offers a powerful and flexible way to interact with your database, abstracting away much of the SQL complexity. By breaking down each part—retrieving, inserting, updating, deleting, filtering, and more—you can easily manage your database interactions in a clean and organized way.

This example provides a basic guide to Query Builder. As your application grows, you can use more advanced features such as joins, subqueries, and eager loading with Eloquent.