runopencode / query-resources-loader-bundle
Provides you with possibility to keep long queries in separate files in 'query' directory in your project.
Installs: 5 092
Dependents: 0
Suggesters: 0
Security: 0
Stars: 6
Watchers: 5
Forks: 0
Open Issues: 3
Type:symfony-bundle
Requires
- php: ^8.2
- ext-ctype: *
- doctrine/dbal: ^2.0|^3.0|^4.0
- doctrine/doctrine-bundle: ^1.0|^2.0
- doctrine/orm: ^2.0|^3.0|^4.0
- doctrine/persistence: ^1.3|^2.0|^3.0|^4.0
- symfony/cache: ^4.0|^5.0|^6.0|^7.0
- symfony/cache-contracts: ^1.0|^2.0|^3.0
- symfony/config: ^4.0|^5.0|^6.0|^7.0
- symfony/dependency-injection: ^4.0|^5.0|^6.0|^7.0
- symfony/doctrine-bridge: ^4.0|^5.0|^6.0|^7.0
- symfony/finder: ^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)
- bamarni/composer-bin-plugin: ^1.8
- roave/security-advisories: dev-latest
- symfony/monolog-bundle: ^1.0|^2.0|^3.0
- dev-master
- 8.0.2
- 8.0.1
- 8.0.0
- 7.0.4
- 7.0.3
- 7.0.2
- 7.0.1
- 7.0.0
- 6.2.0
- 6.1.0
- 6.0.2
- 6.0.1
- 6.0.0
- 5.0.2
- 5.0.1
- 5.0.0
- 4.0.3
- 4.0.2
- 4.0.1
- 4.0.0
- 3.0.2
- 3.0.1
- 3.0.0
- 2.1.5
- 2.1.4
- 2.1.3
- 2.1.2
- 2.1.1
- 2.1.0
- 2.0.6
- 2.0.5
- 2.0.4
- 2.0.3
- 2.0.2
- 2.0.1
- 2.0.0
- 0.5.0
- dev-dependabot/composer/twig/twig-3.11.2
- dev-dependabot/composer/symfony/http-foundation-7.1.7
- dev-dependabot/composer/twig/twig-3.14.0
This package is auto-updated.
Last update: 2024-11-06 20:46:49 UTC
README
The purpose of query resources loader is to help you manage and organize your big, long, database queries, especially in application that deals with reporting.
Features:
- Store your queries in separate,
*.sql
files (or*sql.twig
files), in your project directory or any other directory that you want to use. - Load or execute your queries using
RunOpenCode\Bundle\QueryResourcesLoader\Contract\QueryResourcesLoaderInterface
service. - Full compatibility with Doctrine Dbal. You can move your current queries within repository classes to separate SQL
files and use query loader to execute them. Result of execution is instance of
Doctrine\DBAL\Driver\Result
. Of course, there are neat methods which you can utilize to fetch data from result set, such asgetSingleScalarResult()
,getSingleResult()
,getScalarResult()
, etc... SeeRunOpenCode\Bundle\QueryResourcesLoader\Executor\Dbal\DoctrineDbalExecutionResult
class for more details. - Automatically registers
%kernel.project_dir%/query
directory as query resources directory, as well as allquery
directories withinResources
directories of your bundles. - Integrated with Twig, so you can use Twig syntax in your queries. You can use this feature to build complex
queries, depending on your application logic. Beside control flow statements, you can use all Twig filters, functions,
tests and blocks as well. With
{% include %}
,{% embed %}
,{% use %}
and{% extends %}
statements, you can reuse your queries and build complex queries from smaller ones. - Transactions. You can execute your queries within transaction. Supports
transactional()
API from Doctrine Dbal. You can control transaction isolation level for current statements within transaction. - Distributed transactions. You can execute multiple queries within same transaction against different databases. If any of statements fail, transaction will be rolled back for all databases.
- Caching. You can cache your query results, so they are not loaded from database on each execution.
- Middlewares. You can use middlewares to manipulate query before execution, or to manipulate result after execution. You can switch to other database if query fails, you can add monitoring, logging, load balancing on several databases, etc...
Read the documentation here.
Quick example
Typical reporting repository that has a query string within repository can be implemented like as in example below:
declare(strict_types=1); namespace App\Repository; use Doctrine\DBAL\Connection; use Doctrine\DBAL\Types\Types; final readonly class MyReportingRepository { public function __construct(private Connection $connection) { } public function getInvoicingReport(\DateTimeInterface $from): iterable { $sql = 'SELECT field_1.T as f1, field_2.T as f2, ... field_57.X as f57, ... field_n.N as fn FROM table_name T INNER JOIN table_name_2 T2 ON (T.id = T2.t1_id) INNER JOIN table_name_3 T3 ON (T2.id = T3.t2_id) .... [More joins] WHERE T.create_at >= :from [A lot of where statements and so on...] '; return $this->connection->execute($sql, [ 'from' => $from ], [ 'from' => Types::DATE_IMMUTABLE ]); } }
This is terrible, as it mixes SQL with PHP code, and it is hard to maintain!
With this bundle, you can store your queries in %kernel.project_dir%/query
directory as standard .sql
file (or
.sql.twig
if you use Twig, or any other extension that your query language uses) and load it and execute it using
RunOpenCode\Bundle\QueryResourcesLoader\Contract\QueryResourcesLoaderInterface
service, thus, decreasing amount of
code in your repository classes:
declare(strict_types=1); namespace App\Repository; use RunOpenCode\Bundle\QueryResourcesLoader\Contract\QueryResourcesLoaderInterface; use RunOpenCode\Bundle\QueryResourcesLoader\Executor\Dbal\DbalParameters; final readonly class MyReportingRepository { public function __construct(private QueryResourcesLoaderInterface $loader) { } public function getInvoicingReport(\DateTimeInterface $from): iterable { return $this->loader->execute('invoicing_report.sql', DbalParameters::create()->dateTimeImmutable('from', $from)); } }
Building complex queries
Sometimes, you will need a possibility to build up your queries depending on your application logic. For that purpose, query loader uses Twig and all your query resources are pre-parsed with Twig, allowing you to dynamically build your queries, per example:
# file: my_query.sql.twig SELECT * FROM my_table T WHERE T.field_1 = :some_parameter {% if some_other_parameter is defined %} AND T.field_2 = :some_other_parameter {% endif %}
For other details about this bundle, as well as for tips on how to use it, read the documentation here.
TODO
- Add profiling for middlewares and query execution.