Saucer Laravel Package

Getting Started

Minimal Setup

1. Installation Add the package via Composer:

   composer require boson-php/saucer
   

   No service provider or facade is required—use it as a standalone utility.

2. First Use Case: Basic Query Building Import the Saucer class and start building queries:

   use Boson\Saucer\Saucer;
   
   $query = Saucer::select('users.*')
       ->from('users')
       ->where('active', true)
       ->limit(10);
   

   Outputs a raw SQL string (or array for parameterized queries):

   SELECT users.* FROM users WHERE active = ? LIMIT 10
   

3. Where to Look First

   • Core Methods: select(), from(), where(), join(), orderBy(), limit(), groupBy().
   • Query Types: insert(), update(), delete().
   • Advanced: raw(), subquery(), union(), having().
   • Docs: Check the Boson framework’s documentation (since this is a subtree split).

Implementation Patterns

1. Query Composition

Use method chaining for fluent query building:

$query = Saucer::select('id', 'name')
    ->from('products')
    ->where('price', '>', 100)
    ->orderBy('name', 'asc')
    ->limit(5);

2. Dynamic Conditions

Leverage closures for dynamic where clauses:

$status = 'published';
$query = Saucer::select('posts.*')
    ->from('posts')
    ->where(fn($q) => $q->where('status', $status)->orWhere('draft', true));

3. Joins and Subqueries

• Joins:

  Saucer::select('users.*', 'orders.total')
      ->from('users')
      ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
      ->whereRaw('orders.total > 0');
  

• Subqueries:

  $subquery = Saucer::select('user_id')->from('orders')->where('status', 'completed');
  Saucer::select('users.*')
      ->from('users')
      ->whereIn('id', $subquery);
  

4. Raw SQL and Parameters

Use raw() for database-specific functions or escaping:

Saucer::select('COUNT(*) as total')
    ->from('users')
    ->whereRaw('created_at > NOW() - INTERVAL ? DAY', [30]);

5. Integration with Laravel

Since this is a low-level package, pair it with Laravel’s DB facade for execution:

use Illuminate\Support\Facades\DB;

$query = Saucer::select('*')->from('users')->where('active', true);
$results = DB::select($query->toSql(), $query->getBindings());

6. Batch Operations

Use for bulk inserts/updates:

$data = [
    ['name' => 'Alice', 'email' => '[email protected]'],
    ['name' => 'Bob', 'email' => '[email protected]'],
];
Saucer::table('users')->insert($data);

Gotchas and Tips

Pitfalls

1. No Direct Execution

   • Saucer builds queries but does not execute them. Use Laravel’s DB or a PDO instance to run the query.
   • Example of wrong usage:

     // ❌ Won't work (no execution)
     Saucer::select('*')->from('users')->get(); // Method doesn't exist
     

2. Parameter Binding Quirks

   • Bindings are not auto-escaped for raw SQL. Always use whereRaw() with placeholders (?) or bind() explicitly.
   • Example of unsafe raw SQL:

     // ❌ SQL Injection risk
     Saucer::whereRaw("username = '$username'"); // Never do this!
     

   • Safe alternative:

     Saucer::whereRaw('username = ?', [$username]);
     

3. No Eloquent Integration

   • This is a raw query builder, not an ORM. Avoid mixing with Eloquent models unless manually converting.

4. Subtree Split Limitations

   • Since this is a subtree of boson-php/boson, some features (e.g., Boson’s query caching or hydration) may not be available. Check the parent repo for context.

Debugging Tips

1. Inspect SQL Before Execution Use toSql() and getBindings() to debug:

   $sql = Saucer::select('*')->from('users')->where('active', true)->toSql();
   $bindings = Saucer::select('*')->from('users')->where('active', true)->getBindings();
   dd($sql, $bindings);
   

2. Closure Scope Issues If using closures in where(), ensure variables are accessible:

   $userId = 1;
   Saucer::select('*')
       ->from('posts')
       ->where(fn($q) => $q->where('user_id', $userId)); // Works
   

3. Database-Specific Syntax Some methods (e.g., lockForUpdate()) may not be implemented. Fall back to whereRaw():

   Saucer::select('*')->from('users')->whereRaw('FOR UPDATE');
   

Extension Points

1. Custom Query Builders Extend Saucer by creating a wrapper class:

   class CustomSaucer extends Saucer {
       public function scopeActive($query) {
           return $query->where('active', true);
       }
   }
   

2. Add Database-Specific Methods Override or extend for PostgreSQL/MySQL quirks:

   Saucer::macro('jsonExtract', function ($column, $path) {
       return $this->addSelect("json_extract($column, '$.$path')");
   });
   

3. Integration with Query Loggers Hook into Laravel’s query logging by tapping into toSql()/getBindings():

   DB::listen(function ($query) {
       if ($query->sql === Saucer::select('*')->from('users')->toSql()) {
           // Log or analyze
       }
   });