Statement Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require atlas/statement
   

   No additional configuration is required—just autoload the package.

2. First Use Case: Build a simple SELECT query:

   use Atlas\Statement\Select;
   
   $select = new Select('users');
   $select->where('active', true);
   $select->limit(10);
   
   echo $select->getSql(); // "SELECT * FROM users WHERE active = ? LIMIT 10"
   

3. Where to Look First:

   • Official Documentation for API reference.
   • src/Statement/ directory for core classes (Select, Insert, Update, Delete).
   • Tests in tests/ for usage examples and edge cases.

Implementation Patterns

Core Workflows

1. Query Building: Chain methods for fluent query construction:

   $select = (new Select('posts'))
       ->where('published_at', '>', now())
       ->orderBy('created_at', 'desc')
       ->limit(50);
   

2. Parameter Binding: Automatically escapes values (works with PDO):

   $select = (new Select('products'))
       ->where('name', 'like', '%' . $searchTerm . '%');
   $stmt = $pdo->prepare($select->getSql());
   $stmt->execute($select->getParams());
   

3. Table-Specific Statements:

   • Insert:

     $insert = (new Insert('users'))
         ->set(['name' => 'John', 'email' => 'john@example.com']);
     

   • Update/Delete:

     $update = (new Update('users'))
         ->where('id', 1)
         ->set(['status' => 'active']);
     

4. Joins and Subqueries:

   $select = (new Select('orders'))
       ->join('users', 'orders.user_id = users.id')
       ->whereSub(
           (new Select('payments'))
               ->whereColumn('orders.id', 'payments.order_id')
               ->where('payments.status', 'paid')
               ->select('COUNT(*) as paid_count'),
           '> 0'
       );
   

5. Dynamic Table/Column Handling: Use getTable() (v1.1+) to inspect the target table:

   $update = new Update('users');
   $table = $update->getTable(); // Returns 'users'
   

Integration Tips

• With Atlas.Query: Pass the statement to Atlas\Query for execution:

  $query = new Atlas\Query($pdo);
  $results = $query->run($select);
  

• With Eloquent: Use for raw queries where Eloquent’s query builder is insufficient:

  DB::select((new Select('complex_view'))->getSql(), $select->getParams());
  

• Custom Drivers: Extend Atlas\Statement\Statement to support non-PDO drivers (e.g., Doctrine DBAL).

Gotchas and Tips

Pitfalls

1. Parameter Binding:

   • Issue: Forgetting to bind parameters manually when using raw PDO. Fix: Always use $statement->getParams() with execute():

     $stmt->execute($select->getParams()); // Correct
     $stmt->execute(); // ❌ Missing params!
     

   • Edge Case: Empty arrays in whereEquals() (fixed in v1.0.1) may cause SQL errors. Test with [] values.

2. LIMIT/OFFSET:

   • Issue: UPDATE/DELETE statements ignore limit()/offset() by default (fixed in v1.0.1). Fix: Explicitly chain them if needed:

     $delete = (new Delete('logs'))
         ->where('created_at', '<', now()->subDays(30))
         ->limit(1000); // Works post-v1.0.1
     

3. Database-Specific Syntax:

   • Issue: Some methods (e.g., raw()) may not translate to all databases. Fix: Use database-specific builders or validate SQL before execution:

     if ($db === 'sqlite') {
         $select->whereRaw('UPPER(name) = ?', ['JOHN']); // SQLite uses UPPER()
     }
     

4. Table/Column Validation:

   • Issue: No built-in validation for table/column existence. Fix: Use migrations or a schema tool (e.g., Laravel Schema) to enforce structure.

Debugging Tips

1. Inspect SQL:

   echo $statement->getSql(); // Debug raw SQL
   print_r($statement->getParams()); // Debug bound params
   

2. PHPStan Annotations:
   • New in v1.1.0, annotations may trigger false positives. Suppress with:

     // @phpstan-ignore-next-line
     $select->where('column', 'value');
     

3. Testing:
   • Use Atlas\Statement\TestCase (if available) or mock PDO for unit tests:

     $pdo = $this->createMock(PDO::class);
     $pdo->method('prepare')->willReturnSelf();
     $pdo->method('execute')->willReturn(true);
     

Extension Points

1. Custom Conditions: Extend Atlas\Statement\Condition to add database-specific functions:

   class MyCustomCondition extends Condition {
       public function jsonContains(string $column, mixed $value): self {
           return $this->whereRaw("JSON_CONTAINS({$column}, ?)", [$value]);
       }
   }
   

2. Statement Decorators: Wrap statements to add pre/post-processing:

   class LoggingStatement {
       public function __construct(private Statement $statement) {}
   
       public function getSql(): string {
           \Log::debug('Executing: ' . $this->statement->getSql());
           return $this->statement->getSql();
       }
   }
   

3. Database-Specific Builders: Create subclasses for database quirks:

   class PostgreSQLSelect extends Select {
       public function jsonbPath(string $column, string $path): self {
           return $this->whereRaw("({$column}) ? {$path}", ['->']);
       }
   }