Query Builder

The QueryBuilder class provides a fluent interface for building and executing database queries with support for MySQL, PostgreSQL, and SQLite.

Basic Usage

// Initialize with database connection
$db = new DB($config);
$query = new QueryBuilder($db);

// Get all records from 'users' table
$users = $query->table('users')->all();

// Get single record with where condition
$user = $query->table('users')->where('id', 1)->first();

// Count records with condition
$count = $query->table('users')->where('active', 1)->count();

// User query helper function
query('users')->where('id', 1)->first();

Core Methods

Table Operations

$query->table('users');

$query->prefix('app_')->table('users'); // Queries 'app_users'

$query->table('logs')->truncate();

Select Operations

$query->select(['id', 'name']);
$query->select('id, name, email');

$query->from('users', 'u');

$query->table('users')->as('u');

Aggregate Functions

$activeUsers = $query->table('users')->where('active', 1)->count();

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

Result Retrieval

$users = $query->table('users')->all();

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

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

$user = $query->table('users')->where('id', 999)->firstOrFail();

Pagination

$paginator = $query->table('posts')
    ->orderDesc('created_at')
    ->paginate(15);

foreach ($paginator as $post) {
    // Display post
}
echo $paginator->links();

CRUD Operations

Inserting Data

// Insert single record
$query->table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);

// Insert multiple records
$query->table('users')->insert([
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com']
]);

// Insert with conflict handling (PostgreSQL/SQLite)
$query->table('users')->insert(
    ['id' => 1, 'name' => 'John Updated'],
    ['conflict' => ['id'], 'update' => ['name']]
);

Updating Data

// Update single record
$query->table('users')
    ->where('id', 1)
    ->update(['name' => 'Updated Name']);

// Update multiple records
$query->table('users')
    ->where('status', 'pending')
    ->update(['status' => 'active']);

// Bulk update
$query->table('users')->bulkUpdate([
    ['id' => 1, 'name' => 'John'],
    ['id' => 2, 'name' => 'Jane']
]);

Deleting Data

// Delete single record
$query->table('users')->where('id', 1)->delete();

// Delete multiple records
$query->table('users')
    ->where('last_login', '<', '2023-01-01')
    ->delete();

Query Conditions

Basic Where Clauses

// Simple equality
$query->where('status', 'active');

// With operator
$query->where('age', '>', 18);

// Multiple conditions
$query->where([
    'status' => 'active',
    'age' => 18
]);

// Raw Conditions
$query->whereRaw('created_at > :date', ['date' => '2023-01-01']);

// OR condition
$query->orWhere('status', 'inactive');

Advanced Where Clauses

// NULL checks
$query->whereNull('deleted_at');
$query->whereNotNull('email');

// IN clauses
$query->whereIn('id', [1, 2, 3]);
$query->whereNotIn('role', ['admin', 'superadmin']);

// BETWEEN
$query->between('age', 18, 30);

// LIKE
$query->like('name', 'John%');
$query->notLike('email', '%@spam.com');

// Find in Set
$query->findInSet('tags', 'tech');

// JSON conditions
$query->findInJson('meta', 'settings.notifications', 'enabled');

// Grouped conditions
$query->grouped(function($q) {
    $q->where('status', 'active')
      ->orWhere('vip', 1);
});

Joins

// Basic join
$query->table('posts')
    ->join('users', 'users.id', '=', 'posts.user_id');

// Left join
$query->table('users')
    ->leftJoin('profiles', 'profiles.user_id', '=', 'users.id');

// Join with alias
$query->table('posts', 'p')
    ->join('users as u', 'u.id', '=', 'p.user_id');

Sorting and Grouping

// Order by
$query->orderBy('created_at', 'DESC');
$query->orderDesc('price');

// Group by
$query->groupBy(['category', 'status']);

// Having
$query->having('COUNT(orders) > 5');

Limiting Results

// Limit
$query->take(10); // Get 10 records

// Limit with offset
$query->limit(20, 10); // Skip 20, get next 10

Data Mappers

// Add data transformation
$query->table('users')
    ->addMapper(function($users) {
        return array_map(function($user) {
            $user->full_name = "{$user->first_name} {$user->last_name}";
            return $user;
        }, $users);
    })
    ->all();

Database-Specific Features

// PostgreSQL RETURNING clause
$result = $query->table('users')
    ->insert(
        ['name' => 'John'],
        ['returning' => 'id']
    );

// MySQL IGNORE
$query->table('users')
    ->insert(
        ['email' => 'existing@example.com'],
        ['ignore' => true]
    );

// SQLite COLLATE
$query->collate('nocase')
    ->table('users')
    ->where('email', 'TEST@example.com'); // Case insensitive

Full Examples

Complex Query Example

$posts = $query->table('posts')
    ->as('p')
    ->select([
        'p.id',
        'p.title',
        'p.content',
        'u.name as author',
        'c.name as category'
    ])
    ->join('users as u', 'u.id', '=', 'p.user_id')
    ->join('categories as c', 'c.id', '=', 'p.category_id')
    ->where('p.published', 1)
    ->where('p.created_at', '>', '2023-01-01')
    ->orderBy('p.created_at', 'DESC')
    ->paginate(15);

Advanced Insert Example

// PostgreSQL upsert with returning
$result = $query->table('products')
    ->insert(
        [
            ['id' => 1, 'name' => 'Laptop', 'stock' => 10],
            ['id' => 2, 'name' => 'Phone', 'stock' => 20]
        ],
        [
            'conflict' => ['id'],
            'update' => ['name', 'stock'],
            'returning' => ['id', 'name']
        ]
    );