Skip to content

Latest commit

 

History

History
406 lines (317 loc) · 7.48 KB

query-builder-basics.md

File metadata and controls

406 lines (317 loc) · 7.48 KB

Query Builder Basics

Learn how to use the PDOdb query builder fluent API.

Overview

PDOdb provides a fluent, chainable API for building queries:

use tommyknocker\pdodb\PdoDb;

$db = new PdoDb('mysql', $config);

$users = $db->find()
    ->from('users')
    ->where('active', 1)
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

Getting Started

1. Start with find()

Every query begins with find():

$query = $db->find();
// Returns a QueryBuilder instance

2. Set the Table

Use from() or table():

$db->find()->from('users');
$db->find()->table('users');  // Alias for from()

3. Build Your Query

Chain methods to build your query:

$db->find()
    ->from('users')
    ->where('status', 'active')
    ->andWhere('age', 18, '>=')
    ->orderBy('name', 'ASC')
    ->limit(20);

4. Execute

Execute the query:

// Get all matching rows
$users = $db->find()->from('users')->get();

// Get first row
$user = $db->find()->from('users')->getOne();

// Get single value
$count = $db->find()
    ->from('users')
    ->select('COUNT(*)')
    ->getValue();

Query Building Flow

1. SELECT Operations

// Basic SELECT
$users = $db->find()
    ->from('users')
    ->select(['id', 'name', 'email'])
    ->get();

// With conditions
$activeUsers = $db->find()
    ->from('users')
    ->where('active', 1)
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

// With JOIN
$userPosts = $db->find()
    ->from('users AS u')
    ->select(['u.name', 'p.title'])
    ->leftJoin('posts AS p', 'p.user_id = u.id')
    ->get();

2. INSERT Operations

// Single insert
$id = $db->find()
    ->table('users')
    ->insert([
        'name' => 'Alice',
        'email' => 'alice@example.com'
    ]);

// Multiple inserts
$count = $db->find()
    ->table('users')
    ->insertMulti([
        ['name' => 'Bob', 'email' => 'bob@example.com'],
        ['name' => 'Carol', 'email' => 'carol@example.com']
    ]);

3. UPDATE Operations

$affected = $db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'name' => 'Alice Updated',
        'updated_at' => Db::now()
    ]);

4. DELETE Operations

$deleted = $db->find()
    ->table('users')
    ->where('active', 0)
    ->andWhere('last_login', '2020-01-01', '<')
    ->delete();

Method Chaining

All query builder methods return $this, allowing method chaining:

$query = $db->find()
    ->from('users AS u')
    ->select(['u.id', 'u.name', 'total' => 'SUM(o.amount)'])
    ->leftJoin('orders AS o', 'o.user_id = u.id')
    ->where('u.active', 1)
    ->andWhere('o.status', 'completed')
    ->groupBy('u.id', 'u.name')
    ->having('SUM(o.amount)', 1000, '>')
    ->orderBy('total', 'DESC')
    ->limit(10);

$results = $query->get();

Common Patterns

SELECT with Conditions

$users = $db->find()
    ->from('users')
    ->where('age', 18, '>')
    ->andWhere('status', 'active')
    ->orWhere('verified', 1)
    ->orderBy('created_at', 'DESC')
    ->get();

INSERT with Returning

// Insert and get the ID
$userId = $db->find()->table('users')->insert([
    'name' => 'Alice',
    'email' => 'alice@example.com'
]);

// Insert multiple and get count
$count = $db->find()->table('users')->insertMulti([
    ['name' => 'Bob', 'email' => 'bob@example.com'],
    ['name' => 'Carol', 'email' => 'carol@example.com']
]);

UPDATE with Conditions

$affected = $db->find()
    ->table('users')
    ->where('last_login', '2020-01-01', '<')
    ->update([
        'status' => 'inactive',
        'updated_at' => Db::now()
    ]);

DELETE with Conditions

$deleted = $db->find()
    ->table('posts')
    ->where('user_id', 5)
    ->andWhere('deleted', 1)
    ->delete();

Execution Methods

GET - All Rows

$users = $db->find()->from('users')->get();
// Returns: array<int, array<string, mixed>>

GETONE - First Row

$user = $db->find()->from('users')->getOne();
// Returns: array<string, mixed>|null

GETCOLUMN - Single Column

$names = $db->find()
    ->from('users')
    ->select('name')
    ->getColumn();
// Returns: array<int, string>

GETVALUE - Single Value

$count = $db->find()
    ->from('users')
    ->select('COUNT(*)')
    ->getValue();
// Returns: int|string|float|null

Query Inspection

Get Generated SQL

$query = $db->find()
    ->from('users')
    ->where('age', 18, '>');

$sqlData = $query->toSQL();
echo "SQL: {$sqlData['sql']}\n";
print_r($sqlData['params']);

Get Last Query

$users = $db->find()->from('users')->get();
echo "Last query: {$db->lastQuery}\n";

Query Options

Fetch Mode

// Return as objects instead of arrays
$users = $db->find()
    ->from('users')
    ->asObject()
    ->get();

foreach ($users as $user) {
    echo $user->name;  // Object property access
}

Query Options

// Add query options (dialect-specific)
$users = $db->find()
    ->from('users')
    ->option('DISTINCT')
    ->get();

// Or multiple options
$users = $db->find()
    ->from('users')
    ->option(['SQL_CALC_FOUND_ROWS', 'HIGH_PRIORITY'])
    ->get();

Advanced Queries

Subqueries

// WHERE IN with subquery
$users = $db->find()
    ->from('users')
    ->whereIn('id', function($query) {
        $query->from('orders')
            ->select('user_id')
            ->where('total', 1000, '>');
    })
    ->get();

// WHERE EXISTS
$users = $db->find()
    ->from('users')
    ->whereExists(function($query) {
        $query->from('orders')
            ->where('user_id', Db::raw('users.id'))
            ->where('status', 'completed');
    })
    ->get();

Complex WHERE Conditions

$users = $db->find()
    ->from('users')
    ->where('status', 'active')
    ->andWhere(function($query) {
        $query->where('age', 18, '>')
             ->orWhere('verified', 1);
    })
    ->get();

Aggregations with GROUP BY

$stats = $db->find()
    ->from('orders AS o')
    ->select([
        'user_id',
        'total_orders' => 'COUNT(*)',
        'total_amount' => 'SUM(amount)',
        'avg_amount' => 'AVG(amount)'
    ])
    ->where('status', 'completed')
    ->groupBy('user_id')
    ->having('COUNT(*)', 5, '>')
    ->orderBy('total_amount', 'DESC')
    ->get();

Best Practices

1. Reuse Query Builder

// Build base query
$baseQuery = $db->find()
    ->from('users')
    ->where('active', 1);

// Reuse for different executions
$recent = $baseQuery->orderBy('created_at', 'DESC')->limit(10)->get();
$popular = $baseQuery->orderBy('views', 'DESC')->limit(10)->get();

2. Use Transactions for Multiple Operations

$db->startTransaction();
try {
    $userId = $db->find()->table('users')->insert(['name' => 'Alice']);
    $db->find()->table('posts')->insert(['user_id' => $userId, 'title' => 'Post']);
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

3. Always Use WHERE for UPDATE/DELETE

// ❌ Bad: No WHERE clause
$db->find()->table('users')->update(['status' => 'deleted']);

// ✅ Good: Always specify WHERE
$db->find()
    ->table('users')
    ->where('id', $id)
    ->update(['status' => 'deleted']);

Next Steps