zjkiza/sql-twig-bundle

The bundle executes raw SQL queries with the flexibility to embed Twig extensions, enabling the dynamic creation of queries using Twig syntax.

v0.9.1 2024-12-14 12:43 UTC

Requires

• php: >=8.1
• doctrine/dbal: ^2.0|^3.0|^4.0
• symfony/config: ^4.0|^5.0|^6.0|^7.0
• symfony/dependency-injection: ^4.0|^5.0|^6.0|^7.0
• symfony/http-kernel: ^4.0|^5.0|^6.0|^7.0
• symfony/twig-bundle: ^4.0|^5.0|^6.0|^7.0
• twig/twig: ^2.0|^3.0

Requires (Dev)

• dama/doctrine-test-bundle: ^8.0
• doctrine/doctrine-bundle: ^2.12
• doctrine/doctrine-fixtures-bundle: ^3.5
• ekino/phpstan-banned-code: ^1.0
• friendsofphp/php-cs-fixer: ^3.52
• matthiasnoback/symfony-dependency-injection-test: ^5.1
• pdepend/pdepend: ^2.16
• phploc/phploc: ^7.0
• phpmd/phpmd: ^2.15
• phpstan/phpstan: ^1.10
• phpstan/phpstan-phpunit: ^1.3
• phpstan/phpstan-symfony: ^1.3
• phpunit/phpunit: ^9.6
• psalm/plugin-phpunit: ^0.19.0
• ramsey/uuid-doctrine: ^2.0
• rector/rector: ^1.0
• symfony/framework-bundle: ^7.0
• symfony/phpunit-bridge: ^6.0
• symfony/property-info: ^7.0
• vimeo/psalm: ^5.6.0

MIT

• Zoran Jankovic

README

The bundle executes raw SQL queries with the flexibility to embed Twig extensions, enabling the dynamic creation of queries using Twig syntax.

About the bundle

• You can use Twig syntax when creating queries.
• You place queries in separate files. (Ex: all_media.sql.twig).
• Execute your queries using Zjk\SqlTwig\Contract\SqlTwigInterface service.
• Result of execution Zjk\SqlTwig\Contract\SqlTwigInterface->executeQuery(..) is instance of Doctrine\DBAL\Driver\Result, use their methods to get results.
• Query execution via transaction Zjk\SqlTwig\Contract\SqlTwigInterface->transaction(..)

Installation

Add "zjkiza/sql-twig-bundle" to your composer.json file:

composer require zjkiza/sql-twig-bundle

Symfony integration

Bundle wires up all classes together and provides method to easily setup.

1. Register bundle within your configuration (i.e: bundles.php).

   <?php
   
   declare(strict_types=1);
   
   return [
       // other bundles
       Zjk\SqlTwig\ZJKizaSqlTwigBundle::class =>  ['all' => true],
   ];

Working with the bundle

It is necessary to define which directory/directories will be used for storing files with sql queries.

    twig:
      paths:
        '%kernel.project_dir%/src/sql/media': 'media'
        '%kernel.project_dir%/src/sql/expert': 'expert'

Create a sql query. Example (all_media.sql.twig):

   SELECT
       m.id
   {% if true == user  %}
       , u.name as user_name
   {% endif %}
   
   FROM media as m
   
       {% if true == user  %}
           INNER JOIN user as u ON m.user_id = u.id
       {% endif %}
   
   WHERE m.id in (:ids)
   
   ORDER BY m.id

Working in php, Example:

namespace App\Example;

use Zjk\SqlTwig\Contract\SqlTwigInterface;

class MyRepository {
    
    private SqlTwigInterface $sqlTwig;
    
    public function __construct(SqlTwigInterface $sqlTwig) 
    {
        $this->sqlTwig = $sqlTwig;
    }
    
    public function allMedia(): array
    {
        return $this->sqlTwig->executeQuery('@media/all_media.sql.twig', [
                'user' => true,
                'ids' => ['60b16643-d5e0-468a-8823-499fcf07684a', '60b16643-d5e0-468a-8823-499fcf07684b'],
               ],[
                 'ids' => ArrayParameterType::STRING,
              ])->fetchAllAssociative()
    }
    
    public function withTransaction(): array
    {
        $result = $this->manager->transaction(
            static fn (SqlTwigInterface $manager): Result => $manager->executeQuery('@query/media_id_title.sql.twig'),
            TransactionIsolationLevel::READ_UNCOMMITTED
        );
        
        return $result->fetchAllAssociative();
    }
        
}