subqueries.md

Subqueries

Learn how to use subqueries in WHERE, SELECT, and JOIN clauses.

WHERE IN with Subquery

Using Callable

// Find users who have placed orders > $1000
$users = $db->find()
    ->from('users')
    ->whereIn('id', function($query) {
        $query->from('orders')
            ->select('user_id')
            ->where('total', 1000, '>');
    })
    ->get();

Using QueryBuilder Instance

// Build subquery first
$subquery = $db->find()
    ->from('orders')
    ->select('user_id')
    ->where('total', 1000, '>');

// Use in main query
$users = $db->find()
    ->from('users')
    ->where('id', $subquery, 'IN')
    ->get();

WHERE NOT IN with Subquery

// Find users who have never ordered
$users = $db->find()
    ->from('users')
    ->whereNotIn('id', function($query) {
        $query->from('orders')->select('user_id');
    })
    ->get();

WHERE EXISTS

Simple EXISTS

// Find users who have orders
$users = $db->find()
    ->from('users')
    ->whereExists(function($query) {
        $query->from('orders')
            ->where('user_id', 'users.id')
            ->where('status', 'completed');
    })
    ->get();

NOT EXISTS

// Find users with no orders
$users = $db->find()
    ->from('users')
    ->whereNotExists(function($query) {
        $query->from('orders')
            ->where('user_id', 'users.id');
    })
    ->get();

External Reference Detection

PDOdb automatically detects external table references:

// Automatic detection - no manual wrapping needed
$users = $db->find()
    ->from('users')
    ->whereExists(function($query) {
        $query->from('orders')
            ->where('user_id', 'users.id')
            ->where('total', 'users.balance', '>')
            ->where('status', 'completed');
    })
    ->get();

Subquery in SELECT

Scalar Subquery

// Get user with order count
$users = $db->find()
    ->from('users AS u')
    ->select([
        'u.id',
        'u.name',
        'order_count' => function($q) {
            $q->from('orders')
              ->select(Db::count())
              ->where('user_id', 'u.id');
        }
    ])
    ->get();

Using Db::raw() for Complex Subqueries

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users AS u')
    ->select([
        'u.id',
        'u.name',
        'total_orders' => Db::raw('(
            SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id
        )'),
        'total_spent' => Db::raw('(
            SELECT COALESCE(SUM(total), 0) FROM orders o WHERE o.user_id = u.id
        )')
    ])
    ->get();

Correlated Subqueries

Correlated EXISTS

// Find products that have been ordered recently
$products = $db->find()
    ->from('products AS p')
    ->whereExists(function($query) {
        $query->from('order_items AS oi')
            ->where('oi.product_id', 'p.id')
            ->where('oi.created_at', Db::now('-30 DAYS'), '>');
    })
    ->get();

Complex Examples

Users with Orders Above Average

// Get average order total
$avgOrder = $db->find()
    ->from('orders')
    ->select(Db::avg('total'))
    ->getValue();

// Find users with orders above average
$users = $db->find()
    ->from('users')
    ->whereIn('id', function($query) use ($avgOrder) {
        $query->from('orders')
            ->select('user_id')
            ->where('total', $avgOrder, '>');
    })
    ->get();

Products Never Ordered

$products = $db->find()
    ->from('products')
    ->whereNotExists(function($query) {
        $query->from('order_items')
            ->where('product_id', 'products.id');
    })
    ->get();

Performance Considerations

Use JOIN Instead of Subquery

// ❌ Slow: Subquery in SELECT
$users = $db->find()
    ->from('users')
    ->select([
        'name',
        'order_count' => Db::raw('(SELECT COUNT(*) FROM orders WHERE user_id = users.id)')
    ])
    ->get();

// ✅ Faster: Use JOIN
$users = $db->find()
    ->from('users AS u')
    ->select([
        'u.name',
        'order_count' => Db::count('o.id')
    ])
    ->leftJoin('orders AS o', 'o.user_id = u.id')
    ->groupBy('u.id', 'u.name')
    ->get();

Next Steps

• SELECT Operations - SELECT queries
• Joins - JOIN operations
• Filtering Conditions - WHERE clauses