Pixie Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require pecee/pixie
   

   Add to composer.json if not using autoload:

   "autoload": {
       "psr-4": {
           "App\\": "app/",
           "Pixie\\": "vendor/pecee/pixie/src/"
       }
   }
   

   Run composer dump-autoload.

2. First Connection: Define a connection in config/database.php (or manually):

   use Pixie\Connection;
   use Pixie\Connection\MySQL\MySQLConnection;
   
   $connection = new Connection(
       new MySQLConnection(),
       [
           'host' => 'localhost',
           'database' => 'your_db',
           'username' => 'user',
           'password' => 'pass',
       ]
   );
   

3. First Query:

   $users = $connection->table('users')->get();
   // Returns array of results
   

First Use Case: CRUD Operations

// Create
$connection->table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);

// Read
$users = $connection->table('users')->where('active', true)->get();

// Update
$connection->table('users')->where('id', 1)->update(['name' => 'Jane Doe']);

// Delete
$connection->table('users')->where('id', 1)->delete();

Implementation Patterns

Core Workflows

1. Query Chaining: Pixie supports fluent chaining like Laravel:

   $result = $connection
       ->table('orders')
       ->where('status', 'pending')
       ->where('created_at', '>', now()->subDays(7))
       ->orderBy('total', 'desc')
       ->limit(10)
       ->get();
   

2. Table Aliases:

   $result = $connection
       ->table('users as u')
       ->join('posts as p', 'u.id', '=', 'p.user_id')
       ->select('u.name', 'p.title')
       ->get();
   

3. Subqueries:

   $subQuery = $connection->table('orders')->where('status', 'pending')->select('user_id');
   $result = $connection
       ->table('users')
       ->whereIn('id', $subQuery)
       ->get();
   

4. Raw Expressions:

   use Pixie\Query\Expressions\Raw;
   
   $result = $connection
       ->table('users')
       ->where(new Raw('DATE(created_at)', '>', '2023-01-01'))
       ->get();
   

Integration Tips

1. Laravel-like Service Provider: Create a singleton connection in a service provider:

   $this->app->singleton('pixie', function ($app) {
       return new Connection(
           new MySQLConnection(),
           $app['config']['database.connections.mysql']
       );
   });
   

2. Repository Pattern: Encapsulate queries in repositories:

   class UserRepository {
       protected $connection;
   
       public function __construct(Connection $connection) {
           $this->connection = $connection;
       }
   
       public function getActiveUsers() {
           return $this->connection->table('users')->where('active', true)->get();
       }
   }
   

3. Transactions:

   $connection->beginTransaction();
   try {
       $connection->table('users')->insert([...]);
       $connection->table('logs')->insert([...]);
       $connection->commit();
   } catch (\Exception $e) {
       $connection->rollBack();
       throw $e;
   }
   

4. Dynamic Query Building: Use Pixie\Query\Builder for complex logic:

   $query = $connection->table('products');
   if ($request->has('category')) {
       $query->where('category_id', $request->category);
   }
   $result = $query->get();
   

Gotchas and Tips

Common Pitfalls

1. Connection Management:

   • Pixie does not auto-reconnect like Laravel. Ensure connections are reused or reconnected manually.
   • Example of reconnecting:

     $connection->reconnect();
     

2. Raw Expressions:

   • Always escape values in Raw expressions to avoid SQL injection:

     $safeValue = $connection->getPdo()->quote($value);
     $query->where(new Raw("column = ?", [$safeValue]));
     

3. Case Sensitivity:

   • Table/column names are case-sensitive in some databases (e.g., PostgreSQL). Use consistent casing or backticks:

     $connection->table('`users`')->select('`name`');
     

4. Subquery Limitations:

   • Avoid deeply nested subqueries; they can cause performance issues or syntax errors in some databases.

Debugging Tips

1. Enable Logging:

   $connection->setLogger(new \Pixie\Logger\FileLogger('query.log'));
   

   Logs raw SQL queries for debugging.

2. SQL Dumping: Use toSql() to inspect queries:

   $sql = $connection->table('users')->where('active', true)->toSql();
   dd($sql); // Debug the SQL
   

3. Error Handling: Wrap queries in try-catch:

   try {
       $result = $connection->table('users')->delete();
   } catch (\Pixie\Exception\QueryException $e) {
       // Handle error (e.g., log or rethrow)
   }
   

Extension Points

1. Custom Query Events: Listen to query events (e.g., beforeExecute):

   $connection->on('beforeExecute', function ($query) {
       // Modify query or log it
   });
   

2. Database-Specific Features: Use database-specific adapters for advanced features:

   $connection = new Connection(
       new \Pixie\Connection\PostgreSQL\PostgreSQLConnection(),
       $config
   );
   

3. Custom Expressions: Extend Pixie\Query\Expressions\Expression for reusable logic:

   class AgeExpression extends Expression {
       protected $field;
   
       public function __construct($field) {
           $this->field = $field;
       }
   
       public function getValue() {
           return "TIMESTAMPDIFF(YEAR, {$this->field}, CURDATE())";
       }
   }
   // Usage:
   $query->where(new AgeExpression('birthdate'), '>', 18);
   

4. Query Builder Extensions: Add methods to Pixie\Query\Builder via traits or inheritance:

   trait CustomQueryMethods {
       public function scopeActive($query) {
           return $query->where('active', true);
       }
   }
   // Usage:
   $connection->table('users')->active()->get();
   

Performance Optimizations

1. Batch Processing: Use chunk() for large datasets:

   $connection->table('users')->chunk(100, function ($users) {
       // Process batch
   });
   

2. Selective Column Loading: Avoid * for better performance:

   $connection->table('users')->select('id', 'name')->get();
   

3. Index Utilization: Ensure where clauses use indexed columns:

   // Good (indexed)
   $connection->table('users')->where('email', 'user@example.com')->get();
   // Avoid (non-indexed)
   $connection->table('users')->where('name', 'John')->get();