ApnaPHP

Documentation

Query Builder

ApnaPHP provides a powerful and intuitive query builder that allows you to interact with your database using a fluent interface. It supports multiple database drivers and provides a consistent API across all of them.

1. Basic Usage

The query builder is accessible through the db() helper function or directly through the database handler.

<?php

// Using the db() helper function
$users = db()->table('users')->get();

// Using the database handler directly
$database = app()->database();
$users = $database->table('users')->get();

2. Retrieving Data

Select All Records

$users = db()->table('users')->get();

Select Specific Columns

$users = db()->table('users')
    ->select(['name', 'email', 'created_at'])
    ->get();

Select First Record

$user = db()->table('users')->first();

Select Single Value

$userCount = db()->table('users')->count();
$userName = db()->table('users')->value('name');

Select with Limit

$users = db()->table('users')
    ->limit(10)
    ->get();

Select with Offset

$users = db()->table('users')
    ->offset(10)
    ->limit(10)
    ->get();

3. Where Clauses

Basic Where

$users = db()->table('users')
    ->where('status', 'active')
    ->get();

$user = db()->table('users')
    ->where('email', 'john@example.com')
    ->first();

Where with Operators

$users = db()->table('users')
    ->where('age', '>', 18)
    ->get();

$users = db()->table('users')
    ->where('created_at', '>=', '2024-01-01')
    ->get();

$users = db()->table('users')
    ->where('name', 'LIKE', '%John%')
    ->get();

Multiple Where Conditions

$users = db()->table('users')
    ->where('status', 'active')
    ->where('age', '>', 18)
    ->get();

Where In

$users = db()->table('users')
    ->whereIn('role', ['admin', 'editor', 'user'])
    ->get();

Where Not In

$users = db()->table('users')
    ->whereNotIn('status', ['banned', 'suspended'])
    ->get();

Where Between

$users = db()->table('users')
    ->whereBetween('age', [18, 65])
    ->get();

Where Null

$users = db()->table('users')
    ->whereNull('deleted_at')
    ->get();

Where Not Null

$users = db()->table('users')
    ->whereNotNull('email_verified_at')
    ->get();

Where Like

$users = db()->table('users')
    ->whereLike('name', 'John%')
    ->get();

Or Where

$users = db()->table('users')
    ->where('status', 'active')
    ->orWhere('status', 'pending')
    ->get();

4. Ordering and Grouping

Order By

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

$users = db()->table('users')
    ->orderBy('created_at', 'desc')
    ->get();

Multiple Order By

$users = db()->table('users')
    ->orderBy('status', 'asc')
    ->orderBy('name', 'desc')
    ->get();

Group By

$users = db()->table('users')
    ->select(['status', 'COUNT(*) as count'])
    ->groupBy('status')
    ->get();

Having

$users = db()->table('users')
    ->select(['status', 'COUNT(*) as count'])
    ->groupBy('status')
    ->having('count', '>', 5)
    ->get();

5. Aggregates

Count

$userCount = db()->table('users')->count();
$activeUsers = db()->table('users')
    ->where('status', 'active')
    ->count();

Max

$maxAge = db()->table('users')->max('age');

Min

$minAge = db()->table('users')->min('age');

Average

$avgAge = db()->table('users')->avg('age');

Sum

$totalPoints = db()->table('users')->sum('points');

6. Inserting Data

Single Insert

$id = db()->table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
    'created_at' => date('Y-m-d H:i:s'),
    'updated_at' => date('Y-m-d H:i:s')
]);

Multiple Inserts

$ids = db()->table('users')->insert([
    [
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'created_at' => date('Y-m-d H:i:s'),
        'updated_at' => date('Y-m-d H:i:s')
    ],
    [
        'name' => 'Jane Doe',
        'email' => 'jane@example.com',
        'created_at' => date('Y-m-d H:i:s'),
        'updated_at' => date('Y-m-d H:i:s')
    ]
]);

7. Updating Data

Update All Records

$affected = db()->table('users')
    ->where('status', 'inactive')
    ->update(['status' => 'active']);

Update Single Record

$affected = db()->table('users')
    ->where('id', 1)
    ->update([
        'name' => 'John Smith',
        'updated_at' => date('Y-m-d H:i:s')
    ]);

Update with Join

$affected = db()->table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->where('profiles.plan', 'free')
    ->update(['users.status' => 'premium']);

8. Deleting Data

Delete All Records

$deleted = db()->table('users')->delete();

Delete with Conditions

$deleted = db()->table('users')
    ->where('status', 'inactive')
    ->delete();

Delete Single Record

$deleted = db()->table('users')
    ->where('id', 1)
    ->delete();

9. Joins

Inner Join

$users = db()->table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->select(['users.*', 'profiles.bio', 'profiles.avatar'])
    ->get();

Left Join

$users = db()->table('users')
    ->leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->get();

Right Join

$users = db()->table('users')
    ->rightJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->get();

Multiple Joins

$users = db()->table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->join('roles', 'users.role_id', '=', 'roles.id')
    ->select(['users.*', 'profiles.bio', 'roles.name as role_name'])
    ->get();

10. Raw Queries

Raw SQL

$users = db()->query('SELECT * FROM users WHERE status = ?', ['active']);

Raw Where

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

Raw Select

$users = db()->table('users')
    ->selectRaw('COUNT(*) as total, status')
    ->groupBy('status')
    ->get();

11. Transactions

Basic Transaction

db()->transaction(function () {
    db()->table('users')->insert([
        'name' => 'John Doe',
        'email' => 'john@example.com'
    ]);
    
    db()->table('profiles')->insert([
        'user_id' => db()->getLastInsertId(),
        'bio' => 'John\'s profile'
    ]);
});

Manual Transaction

db()->beginTransaction();

try {
    db()->table('users')->insert([
        'name' => 'John Doe',
        'email' => 'john@example.com'
    ]);
    
    db()->table('profiles')->insert([
        'user_id' => db()->getLastInsertId(),
        'bio' => 'John\'s profile'
    ]);
    
    db()->commit();
} catch (Exception $e) {
    db()->rollback();
    throw $e;
}

12. Pagination

Basic Pagination

$users = db()->table('users')
    ->paginate(15); // 15 records per page

// Access pagination data
$users->data; // Array of records
$users->current_page; // Current page number
$users->per_page; // Records per page
$users->total; // Total records
$users->last_page; // Last page number
$users->has_more; // Whether there are more pages

Pagination with Conditions

$users = db()->table('users')
    ->where('status', 'active')
    ->orderBy('created_at', 'desc')
    ->paginate(20);

13. Advanced Queries

Subqueries

$users = db()->table('users')
    ->whereIn('id', function ($query) {
        $query->select('user_id')
              ->from('orders')
              ->where('total', '>', 100);
    })
    ->get();

Exists

$users = db()->table('users')
    ->whereExists(function ($query) {
        $query->select(1)
              ->from('orders')
              ->whereRaw('orders.user_id = users.id');
    })
    ->get();

Union

$query1 = db()->table('users')->select(['name', 'email']);
$query2 = db()->table('admins')->select(['name', 'email']);

$allUsers = $query1->union($query2)->get();

14. Performance Optimization

Index Usage

// Use indexes for better performance
$users = db()->table('users')
    ->where('email', 'john@example.com') // Uses email index
    ->where('status', 'active') // Uses status index
    ->get();

Select Specific Columns

// Only select needed columns
$users = db()->table('users')
    ->select(['id', 'name', 'email'])
    ->get();

Limit Results

// Limit results to prevent memory issues
$users = db()->table('users')
    ->limit(1000)
    ->get();

15. Error Handling

try {
    $users = db()->table('users')
        ->where('status', 'active')
        ->get();
} catch (Exception $e) {
    // Handle database errors
    console_error('Database query failed: ' . $e->getMessage());
    return json(['error' => 'Database error'], 500);
}

16. Best Practices

  • Use Indexes: Ensure your database tables have proper indexes for frequently queried columns.
  • Limit Results: Always use limit() for queries that might return large datasets.
  • Select Specific Columns: Only select the columns you need to reduce memory usage.
  • Use Transactions: Use transactions for operations that need to be atomic.
  • Parameter Binding: Use parameter binding to prevent SQL injection.
  • Error Handling: Always handle database errors gracefully.
  • Query Optimization: Use explain to analyze query performance when needed.

The query builder provides a powerful and flexible way to interact with your database while maintaining security and performance.

Previous
Response Object