Sql Blade Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require zjkiza/sql-blade
   

2. Configure Paths: Add your SQL Blade template directories to config/view.php under the paths key:

   'paths' => [
       resource_path('sql'),
       app_path('Sql'),
   ],
   

3. First Query File: Create a file (e.g., app/Sql/User/select_user.blade.sql) with Blade syntax:

   SELECT * FROM users WHERE id = :id;
   

4. Execute Query: Inject SqlBladeInterface via constructor and run:

   $result = $this->sqlBlade->executeQuery('User.select_user', ['id' => 1]);
   

   Fetch results using Doctrine DBAL methods:

   $rows = $result->fetchAllAssociative();
   

Implementation Patterns

Workflows

1. Dynamic Query Building: Use Blade directives (@if, @isset, @foreach) to conditionally include clauses:

   SELECT * FROM users
   @if($activeOnly)
       WHERE active = 1
   @endif
   LIMIT :limit;
   

   Execute with:

   $this->sqlBlade->executeQuery('User.filter_users', [
       'activeOnly' => true,
       'limit' => 10,
   ]);
   

2. Transaction Management: Wrap multiple queries in a transaction:

   $this->sqlBlade->transaction(function ($sqlBlade) {
       $sqlBlade->executeQuery('User.create_user', ['name' => 'John']);
       $sqlBlade->executeQuery('User.log_action', ['user_id' => 1, 'action' => 'created']);
   });
   

3. Parameter Binding: Bind parameters via the second argument of executeQuery:

   INSERT INTO users (name, email) VALUES (:name, :email);
   

   $this->sqlBlade->executeQuery('User.create_user', [
       'name' => 'Jane',
       'email' => 'jane@example.com',
   ]);
   

4. Reusable Components: Store complex queries in separate files (e.g., app/Sql/Common/paginate.blade.sql) and include them:

   @include('Common.paginate', ['limit' => 10, 'offset' => 0])
   

Integration Tips

• Laravel Eloquent: Use alongside Eloquent for hybrid approaches (e.g., raw SQL for complex joins + Eloquent for ORM).
• Testing: Mock SqlBladeInterface in unit tests to isolate query logic:

  $this->mock(SqlBladeInterface::class)->shouldReceive('executeQuery')->once();
  

• Caching: Cache compiled query results if queries are static (e.g., using Laravel’s cache helpers).

Gotchas and Tips

Pitfalls

1. Semicolon Requirement: Forgetting ; at the end of SQL files causes silent failures. Validate files with:

   grep -L ';$' app/Sql/*.sql
   

2. Path Configuration: Misconfigured config/view.php paths lead to FileNotFoundException. Verify paths with:

   $this->sqlBlade->getQueryPaths(); // Debug paths
   

3. Parameter Binding: Unbound parameters (e.g., :missing_param) throw ParameterNotFoundException. Validate inputs before execution.

4. Blade Parsing Quirks:

   • Avoid @ symbols in SQL comments (e.g., -- @todo). Use -- or /* */ instead.
   • Nested Blade directives (e.g., @foreach inside @if) may require explicit escaping.

Debugging

• Query Dumping: Enable SQL logging in config/database.php:

  'logging' => true,
  

  Check Laravel logs for raw SQL after execution.

• Result Inspection: Use Doctrine DBAL’s debug methods:

  $result->columnCount(); // Check column count
  $result->fetchNumeric(); // Inspect first row
  

Extension Points

1. Custom Directives: Extend Blade syntax by registering custom directives in a service provider:

   Blade::directive('sqlif', function ($expression) {
       return "<?php if({$expression}): ?>";
   });
   

   Usage in .blade.sql:

   @sqlif($condition)
       SELECT * FROM table
   @endif
   

2. Query Preprocessing: Override Zjk\SqlBlade\Services\SqlBlade to modify queries before execution (e.g., add logging):

   public function executeQuery(string $queryName, array $params = []): Result
   {
       $this->logQuery($queryName, $params);
       return parent::executeQuery($queryName, $params);
   }
   

3. Result Transformation: Chain Doctrine DBAL result methods or create a decorator:

   $result = $this->sqlBlade->executeQuery('User.get_all');
   $users = $result->fetchAllAssociative();
   $mapped = collect($users)->map(fn($u) => new UserModel($u));