Skip to content

Latest commit

 

History

History
534 lines (412 loc) · 9.19 KB

select-operations.md

File metadata and controls

534 lines (412 loc) · 9.19 KB

SELECT Operations

Learn how to build SELECT queries with the PDOdb query builder.

Basic SELECT

Get All Rows

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

Get Single Row

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

Get Single Value

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

Get Single Column

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

Selecting Columns

Select All Columns

$users = $db->find()->from('users')->get();
// SQL: SELECT * FROM users

Select Specific Columns

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

// SQL: SELECT id, name, email FROM users

Select with Aliases

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'user_name' => 'name',
        'user_email' => 'email',
        'full_name' => Db::concat('first_name', ' ', 'last_name')
    ])
    ->get();

// SQL: SELECT id, name AS user_name, email AS user_email, 
//      CONCAT(first_name, ' ', last_name) AS full_name 
//      FROM users

Select with Expressions

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'age_plus_10' => 'age + 10',
        'full_name' => "CONCAT(first_name, ' ', last_name)"
    ])
    ->get();

WHERE Conditions

Simple WHERE

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

// SQL: SELECT * FROM users WHERE status = :status
// Parameters: :status => 'active'

WHERE with Comparison Operators

// Greater than
$adults = $db->find()
    ->from('users')
    ->where('age', 18, '>')
    ->get();

// Less than or equal
$seniors = $db->find()
    ->from('users')
    ->where('age', 65, '<=')
    ->get();

// Not equal
$inactive = $db->find()
    ->from('users')
    ->where('status', 'active', '!=')
    ->get();

Multiple WHERE Conditions

$users = $db->find()
    ->from('users')
    ->where('status', 'active')
    ->andWhere('age', 18, '>=')
    ->andWhere('email', '%@example.com', 'LIKE')
    ->get();

// SQL: SELECT * FROM users WHERE status = :status 
//      AND age >= :age AND email LIKE :email

OR Conditions

$users = $db->find()
    ->from('users')
    ->where('status', 'active')
    ->orWhere('verified', 1)
    ->get();

// SQL: SELECT * FROM users WHERE status = :status OR verified = :verified

Complex WHERE with Nested Conditions

use tommyknocker\pdodb\helpers\Db;

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

// SQL: SELECT * FROM users WHERE status = :status 
//      AND (age >= :age OR verified = :verified)

ORDER BY

Single Column

$users = $db->find()
    ->from('users')
    ->orderBy('name', 'ASC')
    ->get();

// Or descending
$users = $db->find()
    ->from('users')
    ->orderBy('created_at', 'DESC')
    ->get();

Multiple Columns

$users = $db->find()
    ->from('users')
    ->orderBy('status', 'ASC')
    ->orderBy('created_at', 'DESC')
    ->orderBy('name', 'ASC')
    ->get();

// SQL: SELECT * FROM users ORDER BY status ASC, created_at DESC, name ASC

ORDER BY with Expressions

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users')
    ->orderBy(Db::concat('first_name', ' ', 'last_name'), 'ASC')
    ->get();

LIMIT and OFFSET

Limit Results

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

// SQL: SELECT * FROM users ORDER BY created_at DESC LIMIT 10

Pagination with OFFSET

$page = 2;
$perPage = 10;

$users = $db->find()
    ->from('users')
    ->orderBy('name', 'ASC')
    ->limit($perPage)
    ->offset(($page - 1) * $perPage)
    ->get();

// SQL: SELECT * FROM users ORDER BY name ASC LIMIT 10 OFFSET 10

Top N Records

$topUsers = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'score' => 'SUM(points)'
    ])
    ->leftJoin('scores', 'scores.user_id = users.id')
    ->groupBy('users.id', 'users.name')
    ->orderBy('score', 'DESC')
    ->limit(10)
    ->get();

GROUP BY

Basic GROUP BY

$stats = $db->find()
    ->from('orders')
    ->select([
        'user_id',
        'total_orders' => 'COUNT(*)',
        'total_amount' => 'SUM(amount)'
    ])
    ->groupBy('user_id')
    ->get();

Multiple Columns

$sales = $db->find()
    ->from('sales')
    ->select([
        'year',
        'month',
        'region',
        'revenue' => 'SUM(amount)'
    ])
    ->groupBy('year', 'month', 'region')
    ->get();

HAVING

Using HAVING

$highValueCustomers = $db->find()
    ->from('orders')
    ->select([
        'user_id',
        'total' => 'SUM(amount)'
    ])
    ->groupBy('user_id')
    ->having('SUM(amount)', 1000, '>')
    ->get();

// SQL: SELECT user_id, SUM(amount) AS total FROM orders 
//      GROUP BY user_id HAVING SUM(amount) > :having_0

HAVING with Aggregate Functions

$popularProducts = $db->find()
    ->from('order_items')
    ->select([
        'product_id',
        'sales_count' => 'COUNT(*)',
        'total_quantity' => 'SUM(quantity)'
    ])
    ->groupBy('product_id')
    ->having('COUNT(*)', 10, '>=')
    ->orderBy('sales_count', 'DESC')
    ->get();

DISTINCT

Get Distinct Values

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users')
    ->select('email')
    ->option('DISTINCT')
    ->get();

// SQL: SELECT DISTINCT email FROM users

Count Distinct

$uniqueEmails = $db->find()
    ->from('users')
    ->select(Db::count('DISTINCT email'))
    ->getValue();

Common Patterns

Latest Records

$latestPost = $db->find()
    ->from('posts')
    ->where('published', 1)
    ->orderBy('created_at', 'DESC')
    ->limit(1)
    ->getOne();

Random Records

$randomUser = $db->find()
    ->from('users')
    ->orderBy(Db::raw('RAND()'))
    ->limit(1)
    ->getOne();

Top N by Column

$topSellers = $db->find()
    ->from('products AS p')
    ->select([
        'p.name',
        'total_sold' => 'SUM(oi.quantity)',
        'revenue' => 'SUM(oi.quantity * oi.price)'
    ])
    ->leftJoin('order_items AS oi', 'oi.product_id = p.id')
    ->where('oi.status', 'completed')
    ->groupBy('p.id', 'p.name')
    ->orderBy('total_sold', 'DESC')
    ->limit(10)
    ->get();

Fetch Modes

Default (Associative Arrays)

$users = $db->find()->from('users')->get();

foreach ($users as $user) {
    echo $user['name'];  // Array access
}

Objects

$users = $db->find()
    ->from('users')
    ->asObject()
    ->get();

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

Checking Results

EXISTS

$hasActiveUsers = $db->find()
    ->from('users')
    ->where('active', 1)
    ->exists();

// Returns: bool

NOT EXISTS

$noDeletedUsers = $db->find()
    ->from('users')
    ->where('deleted', 1)
    ->notExists();

// Returns: bool

Debugging Queries

Get SQL and Parameters

$query = $db->find()
    ->from('users')
    ->where('age', 25, '>')
    ->andWhere('status', 'active');

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

// Output:
// SQL: SELECT * FROM users WHERE age > :age AND status = :status
// Array (
//     [:age] => 25
//     [:status] => active
// )

Get Last Query

$users = $db->find()->from('users')->get();

echo "Last query: {$db->lastQuery}\n";

Performance Tips

1. Use LIMIT

// ❌ Bad: Could load millions of rows
$users = $db->find()->from('users')->get();

// ✅ Good: Limit results
$users = $db->find()->from('users')->limit(1000)->get();

2. Use Specific Columns

// ❌ Bad: Select all columns
$users = $db->find()->from('users')->get();

// ✅ Good: Select only what you need
$users = $db->find()
    ->from('users')
    ->select(['id', 'name', 'email'])
    ->get();

3. Use Indexes

// Create index for frequently queried column
$db->rawQuery('CREATE INDEX idx_users_email ON users(email)');

// Now this query is fast
$user = $db->find()
    ->from('users')
    ->where('email', 'alice@example.com')
    ->getOne();

4. Use EXPLAIN

$plan = $db->find()
    ->from('users')
    ->where('age', 25, '>')
    ->orderBy('created_at', 'DESC')
    ->explain();

// Check if indexes are used
print_r($plan);

Next Steps