Sqlquery Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require aura/sqlquery
   

   Register the Aura\SqlQuery\QueryFactory in your Laravel service provider (e.g., AppServiceProvider):

   $this->app->singleton('Aura.SqlQuery.QueryFactory', function ($app) {
       return new \Aura\SqlQuery\QueryFactory();
   });
   

2. First Query:

   use Aura\SqlQuery\QueryFactory;
   
   $factory = app(QueryFactory::class);
   $select = $factory->newSelect();
   $select->cols(['id', 'name'])
          ->from('users')
          ->where('active = ?', true);
   
   echo $select->getStatement(); // Outputs: SELECT id, name FROM users WHERE active = ?
   

3. Execute with PDO:

   $pdo = DB::connection()->getPdo();
   $stmt = $pdo->prepare($select->getStatement());
   $stmt->execute($select->getBindValues());
   

First Use Case: Fetching Paginated Users

$factory = app(QueryFactory::class);
$select = $factory->newSelect()
    ->cols(['id', 'name', 'email'])
    ->from('users')
    ->where('active = ?', true)
    ->orderBy('name')
    ->setPaging(10, 20); // Limit 10, offset 20

$stmt = DB::connection()->prepare($select->getStatement());
$stmt->execute($select->getBindValues());
$users = $stmt->fetchAll();

Implementation Patterns

1. Query Composition

• Fluent Interface: Chain methods for readability:

  $select = $factory->newSelect()
      ->cols(['id', 'name'])
      ->from('users')
      ->where('created_at > ?', Carbon::now()->subDays(7))
      ->orderBy('name');
  

• Subqueries:

  $subSelect = $factory->newSelect()
      ->cols(['MAX(id) as max_id'])
      ->from('orders')
      ->where('user_id = ?', $userId);
  
  $select = $factory->newSelect()
      ->cols(['u.id', 'u.name'])
      ->from('users u')
      ->where('u.id IN (?)', $subSelect);
  

2. Database-Specific Features

• MySQL ON DUPLICATE KEY UPDATE:

  $insert = $factory->newInsert();
  $insert->into('users')
         ->cols(['email', 'name'])
         ->rows([['user@example.com', 'John'], ['test@example.com', 'Jane']])
         ->onDuplicateKeyUpdate(['name' => 'updated_name']);
  

• PostgreSQL RETURNING:

  $insert = $factory->newInsert();
  $insert->into('users')
         ->cols(['email', 'name'])
         ->rows([['user@example.com', 'John']])
         ->returning(['id']);
  

3. Integration with Eloquent

• Raw Query Builder:

  $query = DB::table('users')
      ->whereRaw($select->getStatement(), $select->getBindValues());
  

• Custom Accessors:

  $factory = app(QueryFactory::class);
  $user = User::whereRaw($factory->newSelect()
      ->cols(['id'])
      ->from('users')
      ->where('email = ?', 'user@example.com')
      ->getStatement(), $factory->newSelect()->getBindValues())
      ->first();
  

4. Dynamic Query Building

• Conditional Clauses:

  $select = $factory->newSelect()
      ->cols(['id', 'name'])
      ->from('users');
  
  if ($request->has('active')) {
      $select->where('active = ?', true);
  }
  
  if ($request->has('search')) {
      $select->where('name LIKE ?', "%{$request->search}%");
  }
  

• Reusable Query Logic:

  function buildUserSelect($factory, $activeOnly = false) {
      $select = $factory->newSelect()
          ->cols(['id', 'name', 'email'])
          ->from('users');
  
      if ($activeOnly) {
          $select->where('active = ?', true);
      }
  
      return $select;
  }
  

Gotchas and Tips

1. Placeholder Handling

• Named vs. Positional: Aura.SqlQuery converts ? placeholders to named :param placeholders (e.g., :0, :1) to avoid PDO binding issues.

  // Avoid mixing ? and named placeholders in the same query.
  $select->where('id = ? AND name = :name', [$id]); // ❌ Risky
  $select->where('id = :id AND name = :name', ['id' => $id, 'name' => $name]); // ✅ Safe
  

• Subquery Bindings: Bind values from subqueries automatically:

  $subSelect = $factory->newSelect()
      ->cols(['id'])
      ->from('orders')
      ->where('user_id = ?', $userId);
  
  $select = $factory->newSelect()
      ->cols(['u.id', 'u.name'])
      ->from('users u')
      ->where('u.id IN (?)', $subSelect); // Bindings are preserved.
  

2. Table Alias Conflicts

• Duplicate Aliases: Aura.SqlQuery throws an exception if you reuse table aliases:

  $select = $factory->newSelect()
      ->from('users u')
      ->join('orders o', 'u.id = o.user_id')
      ->join('users u', 'o.user_id = u.id'); // ❌ Throws exception
  

• Workaround: Use distinct aliases:

  $select->join('users u2', 'o.user_id = u2.id'); // ✅ Works
  

3. Performance Tips

• Avoid SELECT *: Explicitly define columns to reduce payload:

  $select->cols(['id', 'name']); // ✅ Better than SELECT *
  

• Use getStatement() for Debugging: Inspect SQL before execution:

  echo $select->getStatement(); // Log or dump for debugging
  

4. Debugging Common Issues

• Missing Columns: Aura.SqlQuery throws an exception if no columns are selected:

  $select = $factory->newSelect()->from('users'); // ❌ Throws exception
  $select->cols(['id']); // ✅ Fixes it
  

• Unbound Parameters: Ensure all ? placeholders are bound:

  $select->where('id = ?', $id); // ✅ Bound
  $select->where('id = ?'); // ❌ Unbound (will fail on execution)
  

5. Extension Points

• Custom Query Classes: Extend Aura\SqlQuery\Common\Select or Aura\SqlQuery\Common\Insert for domain-specific logic:

  class UserSelect extends \Aura\SqlQuery\Common\Select {
      public function activeOnly() {
          $this->where('active = ?', true);
          return $this;
      }
  }
  

• Database-Specific Quoting: Override the Quoter for custom identifier quoting:

  $factory->setQuoter(new class extends \Aura\SqlQuery\Common\Quoter {
      public function quoteIdentifier($identifier) {
          return '`' . str_replace('`', '``', $identifier) . '`';
      }
  });
  

6. Laravel-Specific Quirks

• PDO Connection: Use Laravel’s DB::connection()->getPdo() for consistency:

  $pdo = DB::connection('mysql')->getPdo();
  $stmt = $pdo->prepare($select->getStatement());
  

• Transaction Support: Wrap Aura.SqlQuery execution in Laravel transactions:

  DB::transaction(function () use ($select, $factory) {
      $pdo = DB::connection()->getPdo();
      $stmt = $pdo->prepare($select->getStatement());
      $stmt->execute($select->getBindValues());
  });
  

7. Testing Tips

• Mock Queries: Use getStatement() and getBindValues() for unit tests:

  $select = $factory->newSelect()->cols(['id'])->from('users');
  $this->assertEquals('SELECT id FROM users', $select->getStatement());
  $this->assertEquals([], $select->getBindValues());
  

• Database Snapshots: Reset test databases between tests to avoid