Query Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require atlas/query
   

   Requires atlas/pdo (installed automatically as a dependency).

2. First Connection:

   use Atlas\Query\QueryFactory;
   use Atlas\Pdo\Connection;
   
   $pdo = new Connection('mysql:host=localhost;dbname=test', 'user', 'pass');
   $factory = new QueryFactory($pdo);
   

3. First Query:

   $select = $factory->select()
       ->columns(['id', 'name'])
       ->from('users')
       ->whereEquals('active', true);
   
   $results = $select->getStatement()->execute()->fetchAll();
   

Where to Look First

• Official Docs for API reference.
• QueryFactory class for creating query builders.
• Select, Insert, Update, Delete classes for CRUD operations.
• Where and Join classes for filtering and relationships.

Implementation Patterns

Core Workflow

1. Factory Pattern:

   $factory = new QueryFactory($pdo);
   $select = $factory->select(); // or insert(), update(), delete()
   

2. Fluent Interface:

   $users = $factory->select()
       ->columns(['id', 'name', 'email'])
       ->from('users')
       ->where('age', '>', 18)
       ->orderBy('name')
       ->limit(10);
   

3. Binding Values:

   $select = $factory->select()
       ->from('users')
       ->whereEquals('email', $userEmail); // Safely binds values
   

4. Joins:

   $select = $factory->select()
       ->columns(['users.id', 'posts.title'])
       ->from('users')
       ->join('posts', 'users.id', '=', 'posts.user_id');
   

5. Subqueries:

   $subquery = $factory->select()
       ->columns(['max(id) as latest_id'])
       ->from('posts')
       ->where('user_id', $userId);
   
   $select = $factory->select()
       ->from('users')
       ->where('id', 'in', $subquery);
   

Integration Tips

• Laravel Compatibility: Override Laravel’s DB facade to use Atlas\Query:

  // In a service provider
  DB::extend('atlas', function ($config) {
      $pdo = new Connection($config['dsn'], $config['username'], $config['password']);
      return new QueryFactory($pdo);
  });
  

• Repository Pattern:

  class UserRepository {
      protected $factory;
  
      public function __construct(QueryFactory $factory) {
          $this->factory = $factory;
      }
  
      public function findActiveUsers() {
          return $this->factory->select()
              ->from('users')
              ->whereEquals('active', true)
              ->getStatement()
              ->execute()
              ->fetchAll();
      }
  }
  

• Transactions:

  $pdo->beginTransaction();
  try {
      $insert = $factory->insert()
          ->into('orders')
          ->columns(['user_id', 'amount'])
          ->values([$userId, $amount]);
      $insert->getStatement()->execute();
  
      $pdo->commit();
  } catch (\Exception $e) {
      $pdo->rollBack();
      throw $e;
  }
  

Gotchas and Tips

Pitfalls

1. Identifier Quoting: Atlas does not auto-quote table/column names by default (unlike Laravel). Always use quoteIdentifier() for dynamic identifiers:

   $table = 'users';
   $select = $factory->select()
       ->from($factory->quoteIdentifier($table));
   

2. Empty Arrays in whereEquals: Fixed in v1.3.2, but older versions may fail. Ensure:

   // Works in v1.3.2+
   $select->whereEquals('tags', []); // Correctly handles empty arrays
   

3. LIMIT/OFFSET on UPDATE/DELETE: Added in v1.3.2. Older versions ignore these clauses:

   $update = $factory->update()
       ->table('users')
       ->set('active', false)
       ->limit(10); // Works in v1.3.2+
   

4. UNION Bug: Fixed in v1.3.0. If using union() or unionAll(), ensure you’re on v1.3.0+.

5. Binding Context: Atlas uses positional binding (like PDO), not named placeholders. Avoid mixing with Laravel’s ? placeholders if switching between them.

Debugging Tips

• SQL Dumping:

  $sql = $select->getStatement()->getSql();
  $params = $select->getStatement()->getParams();
  // Log $sql and $params for debugging
  

• Parameter Binding: Use sprintf-style binding for clarity:

  $select = $factory->select()
      ->from('users')
      ->where('created_at', '>', 'sprintf("%s", "2023-01-01")');
  

• Connection Issues: Ensure Atlas\Pdo\Connection is properly configured for your DBMS (MySQL/Postgres/SQLite/SQLServer).

Extension Points

1. Custom Select Classes: Override QueryFactory to use custom Select implementations (since v1.0.0-beta2):

   $factory = new QueryFactory($pdo, MyCustomSelect::class);
   

2. Query Modifiers: Extend Atlas\Query\ModifyColumns or Atlas\Query\Where for custom logic:

   class CustomWhere extends Where {
       public function whereCustom($column, $operator, $value) {
           return $this->where(sprintf('%s %s ?', $column, $operator), $value);
       }
   }
   

3. Statement Customization: Override Atlas\Query\Statement to modify SQL generation or add hooks:

   class CustomStatement extends Statement {
       public function getSql() {
           $sql = parent::getSql();
           // Modify SQL here (e.g., add logging)
           return $sql;
       }
   }
   

Performance Tips

• Batch Inserts: Use values() with an array of arrays for bulk inserts:

  $insert = $factory->insert()
      ->into('users')
      ->columns(['name', 'email'])
      ->values([
          ['John', 'john@example.com'],
          ['Jane', 'jane@example.com'],
      ]);
  

• Avoid N+1: Use joins or subqueries instead of nested queries:

  // Bad (N+1)
  $users = $factory->select()->from('users')->getResults();
  foreach ($users as $user) {
      $posts = $factory->select()->from('posts')->where('user_id', $user['id'])->getResults();
  }
  
  // Good (Single query)
  $select = $factory->select()
      ->columns(['users.*', 'posts.title'])
      ->from('users')
      ->join('posts', 'users.id', '=', 'posts.user_id');