Laminas Db Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require laminas/laminas-db
   

2. Configure Adapter (e.g., MySQL):

   use Laminas\Db\Adapter\Adapter;
   use Laminas\Db\Adapter\Driver\Pdo\Pdo;
   
   $driver = new Pdo\Pdo\Connection(
       new Pdo\Pdo\Connection\ConnectionOptions(
           'mysql:host=localhost;dbname=test',
           'username',
           'password'
       )
   );
   
   $adapter = new Adapter(
       $driver,
       new Pdo\Pdo\Result(),
       new Pdo\Pdo\Statement(),
       new Pdo\Pdo\Platform\MySql()
   );
   

3. First Query:

   use Laminas\Db\Sql\Sql;
   
   $sql = new Sql($adapter);
   $select = $sql->select()->from('users')->where(['id' => 1]);
   $result = $adapter->query($sql->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
   $users = $result->buffer();
   

Key First Use Case

Fetch a single user by ID:

$sql = new Sql($adapter);
$select = $sql->select()->from('users')->where(['id' => 1]);
$statement = $sql->prepareStatementForSqlObject($select);
$row = $statement->execute()->current();

Implementation Patterns

Query Building Workflow

1. Create SQL Object:

   $sql = new Sql($adapter);
   

2. Build Query:

   $select = $sql->select()
       ->from('posts')
       ->where(['author_id' => 1])
       ->order('created_at DESC')
       ->limit(10);
   

3. Execute:

   $statement = $sql->prepareStatementForSqlObject($select);
   $results = $statement->execute();
   

Common Patterns

• Table-Specific SQL:

  $sql = new Sql($adapter, 'users'); // Binds to 'users' table
  $select = $sql->select()->where(['active' => 1]); // Implicit 'from(users)'
  

• Complex Joins:

  $select = $sql->select()
      ->from('orders')
      ->join('users', 'orders.user_id = users.id', ['name', 'email'], Select::JOIN_LEFT)
      ->where(['orders.status' => 'completed']);
  

• Dynamic Conditions:

  $select->where(function (Where $where) {
      if ($searchTerm) {
          $where->like('title', "%{$searchTerm}%");
      }
  });
  

• Bulk Operations:

  $insert = $sql->insert()->into('logs')->values([
      ['message' => 'Test', 'created_at' => date('Y-m-d H:i:s')],
      ['message' => 'Another', 'created_at' => date('Y-m-d H:i:s')],
  ]);
  $statement = $sql->prepareStatementForSqlObject($insert);
  $statement->execute();
  

Integration with Laravel

1. Adapter Wrapper:

   use Laminas\Db\Adapter\Adapter as LaminasAdapter;
   use Illuminate\Database\Connection;
   
   $laminasAdapter = new LaminasAdapter(
       $pdoConnection,
       $resultFactory,
       $statementFactory,
       $platform
   );
   
   // Use in Laravel's query builder or raw queries
   

2. Hybrid Approach:

   // Use Laravel's query builder for simplicity, fall back to Laminas for complex SQL
   $users = DB::table('users')->where('active', 1)->get();
   
   // For advanced SQL (e.g., window functions)
   $sql = new Sql($laminasAdapter);
   $select = $sql->select()
       ->from('users')
       ->columns([
           'user_id',
           new \Laminas\Db\Sql\Expression('RANK() OVER (ORDER BY created_at DESC) as rank')
       ]);
   

Gotchas and Tips

Pitfalls

1. Platform-Specific SQL:

   • Always pass the correct PlatformInterface (e.g., MySql, Postgres) to avoid syntax errors.
   • Example:

     $sql->buildSqlString($select, new Pdo\Pdo\Platform\Postgres());
     

2. Parameter Binding:

   • Use Expression for raw SQL to avoid accidental binding:

     // ❌ Avoid (binds parameters unexpectedly)
     $select->where("UPPER(name) = '{$name}'");
     
     // ✅ Safe
     $select->where(new \Laminas\Db\Sql\Expression("UPPER(name) = '{$name}'"));
     

3. Result Handling:

   • buffer() fetches all rows; use execute() + fetch() for large datasets:

     $statement = $sql->prepareStatementForSqlObject($select);
     while ($row = $statement->execute()->fetch()) {
         // Process row-by-row
     }
     

4. Transaction Management:

   • Laminas DB does not manage transactions directly. Use the adapter's beginTransaction():

     $adapter->beginTransaction();
     try {
         $insert->execute();
         $adapter->commit();
     } catch (\Exception $e) {
         $adapter->rollBack();
         throw $e;
     }
     

Debugging Tips

1. Log SQL Queries:

   $sqlString = $sql->buildSqlString($select);
   \Log::debug($sqlString); // Laravel example
   

2. Check Platform Compatibility:

   • Test queries on the target database early. Some functions (e.g., CONCAT_WS) behave differently across platforms.

3. Use getSqlString() for Inspection:

   $sqlString = $select->getSqlString();
   

Extension Points

1. Custom Predicates:

   use Laminas\Db\Sql\Predicate\PredicateInterface;
   
   class CustomPredicate implements PredicateInterface {
       public function __construct(private string $field, private string $value) {}
       public function toString(): string {
           return "{$this->field} LIKE '%{$this->value}%'";
       }
   }
   
   $select->where(new CustomPredicate('name', 'John'));
   

2. Override Platform Behavior:

   • Extend Laminas\Db\Adapter\Platform\PlatformInterface for custom SQL generation.

3. Integrate with ORMs:

   • Use TableDataGateway or RowDataGateway for object-relational mapping:

     use Laminas\Db\TableGateway\TableGateway;
     
     $tableGateway = new TableGateway('users', $adapter);
     $user = $tableGateway->select(['id' => 1])->current();
     

Laravel-Specific Quirks

1. Adapter Initialization:

   • Laravel's Connection class is not directly compatible. Use a wrapper or initialize Laminas DB separately.

2. Query Builder vs. Laminas DB:

   • Prefer Laravel's query builder for simple CRUD. Use Laminas DB for:
     • Complex SQL (e.g., recursive CTEs, window functions).
     • Multi-database support (e.g., Oracle, DB2).
     • Legacy systems requiring Laminas DB.

3. Service Provider Binding:

   // config/app.php
   'aliases' => [
       'LaminasDb' => Laminas\Db\Adapter\Adapter::class,
   ],
   

   // app/Providers/AppServiceProvider.php
   public function register() {
       $this->app->singleton('LaminasDb', function ($app) {
           return new Adapter(
               new Pdo\Pdo\Connection(...),
               new Pdo\Pdo\Result(),
               new Pdo\Pdo\Statement(),
               new Pdo\Pdo\Platform\MySql()
           );
       });
   }