v2.6.1

Release v2.6.1: Automatic External Reference Detection 🎯

🚀 What's New

This release introduces automatic external reference detection in subqueries, making complex queries much more intuitive to write. No more manual Db::raw() wrapping for external table references!

✨ Key Features

Automatic External Reference Detection

QueryBuilder now automatically detects and converts external table references (table.column) to RawValue objects:

• Works in: where(), select(), orderBy(), groupBy(), having() methods
• Smart detection: Only converts references to tables not in current query's FROM clause
• Alias support: Works with table aliases (e.g., u.id where u is an alias)
• Pattern matching: Detects table.column or alias.column patterns
• Safe processing: Invalid patterns (like 123.invalid) are not converted

New Helper: Db::ref()

Added Db::ref() helper for manual external references (though now mostly unnecessary):

Db::ref('users.id')  // Equivalent to Db::raw('users.id')

📝 Examples

Before (v2.6.0)

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

After (v2.6.1)

$users = $db->find()
    ->from('users')
    ->whereExists(function($query) {
        $query->from('orders')
            ->where('user_id', 'users.id')  // ✨ Automatic detection!
            ->where('status', 'completed');
    })
    ->get();

More Examples

SELECT with external references:

$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'total_orders' => 'COUNT(orders.id)',     // Auto-detected
        'last_order' => 'MAX(orders.created_at)' // Auto-detected
    ])
    ->leftJoin('orders', 'orders.user_id = users.id')
    ->groupBy('users.id', 'users.name')
    ->get();

ORDER BY with external references:

$users = $db->find()
    ->from('users')
    ->select(['users.id', 'users.name', 'total' => 'SUM(orders.amount)'])
    ->leftJoin('orders', 'orders.user_id = users.id')
    ->groupBy('users.id', 'users.name')
    ->orderBy('total', 'DESC')  // Auto-detected
    ->get();

With table aliases:

$users = $db->find()
    ->from('users AS u')
    ->whereExists(function($query) {
        $query->from('orders AS o')
            ->where('o.user_id', 'u.id')  // Auto-detected with aliases
            ->where('o.status', 'completed');
    })
    ->get();

🧪 Test Coverage

• 39 new tests across all dialects (MySQL, PostgreSQL, SQLite)
• 13 tests per dialect covering all scenarios:
  • whereExists and whereNotExists with external references
  • select expressions with external references
  • orderBy and groupBy with external references
  • having clauses with external references
  • Internal references (not converted)
  • Aliased table references
  • Complex external references
  • Edge cases and invalid patterns

🔧 Technical Details

• All tests passing: 429+ tests, 2044+ assertions
• PHPStan Level 8: Zero errors across entire codebase
• All examples passing: 24/24 examples on all database dialects
• Backward compatibility: Fully maintained - existing code continues to work unchanged
• Performance: Minimal overhead - only processes string values matching table.column pattern

🎯 Detection Rules

• Pattern: table.column or alias.column
• Scope: Only converts if the table/alias is not in the current query's FROM clause
• Methods: Works in where(), select(), orderBy(), groupBy(), having()
• Safety: Internal references (tables in current query) are not converted
• Validation: Invalid patterns (like 123.invalid) are not converted

📚 Documentation Updates

• Updated README.md with comprehensive examples
• Added new "Automatic External Reference Detection" section
• Updated subquery examples to demonstrate automatic detection
• Added Db::ref() to helper functions reference

🔄 Migration Guide

No migration required! This is a fully backward-compatible release. Existing code continues to work unchanged.

If you want to take advantage of the new automatic detection, simply remove Db::raw() wrappers around external table references:

// Old way (still works)
->where('user_id', Db::raw('users.id'))

// New way (automatic detection)
->where('user_id', 'users.id')

🎉 Benefits

1. Cleaner code: No more Db::raw() wrappers for simple external references
2. Better readability: Natural SQL syntax in subqueries
3. Reduced errors: Automatic detection prevents common mistakes
4. Consistent behavior: Works the same across all database dialects
5. Zero breaking changes: Existing code continues to work

---

Full Changelog: v2.6.0...v2.6.1

Installation: composer require tommyknocker/pdo-database-class:^2.6.1