soyuka / esql
PHP Extended Structured Query Language
Installs: 1 300
Dependents: 0
Suggesters: 0
Security: 0
Stars: 91
Watchers: 4
Forks: 4
Open Issues: 3
Requires
- jms/parser-lib: ^1.0
- phpmyadmin/sql-parser: ^5.4
- symfony/dependency-injection: ^6.0
Requires (Dev)
- api-platform/core: dev-fix/jsonld-property-metadata-iris
- doctrine/orm: ^2.8
- friendsofphp/php-cs-fixer: ^3.3
- hautelook/alice-bundle: ^2.8
- jane-php/automapper: ^7.2
- jane-php/automapper-bundle: ^7.2
- phpdocumentor/reflection-docblock: ^5.2
- phpspec/prophecy-phpunit: ^2.0
- rector/rector: ^0.12.21
- symfony/browser-kit: ^5.2
- symfony/config: ^5.2
- symfony/http-client: ^5.2
- symfony/http-kernel: ^5.2
- symfony/phpunit-bridge: ^5.2
- symfony/profiler-pack: ^1.0
- symfony/serializer: ^5.2
- symfony/uid: ^5.2
- vimeo/psalm: ^4.3
Suggests
- api-platform/core: To use the API Platform bridge.
- doctrine/orm: To use doctrine metadata.
- jane-php/automapper: To use JanePHP for automapping.
- phpdocumentor/reflection-docblock: To support extracting metadata from PHPDoc.
- symfony/serializer: To use Symfony for automapping.
This package is auto-updated.
Last update: 2024-10-25 16:31:56 UTC
README
PHP Extended SQL is an alternative to the also-known DQL (Doctrine Query Language). It combines the flexibility of SQL with the powerful Doctrine metadata to give you more control over queries.
<?php use App\Entity\Car; use App\Entity\Model; use Soyuka\ESQL\Bridge\Doctrine\ESQL; use Soyuka\ESQL\Bridge\Automapper\ESQLMapper; $connection = $managerRegistry->getConnection(); $mapper = new ESQLMapper($autoMapper, $managerRegistry); $esql = new ESQL($managerRegistry, $mapper); $car = $esql(Car::class); $model = $car(Model::class); $query = <<<SQL SELECT {$car->columns()}, {$model->columns()} FROM {$car->table()} INNER JOIN {$model->table()} ON {$car->join(Model::class)} WHERE {$car->identifier()} SQL; $stmt = $connection->prepare($query); $stmt->execute(['id' => 1]); var_dump($esql->map($stmt->fetch()));
Jump to the documentation or read this blog article to see it in action.
API Platform bridge
This package comes with an API Platform bridge that supports filters and pagination. To use our bridge, use the esql
attribute:
<?php use ApiPlatform\Metadata\ApiResource; use Soyuka\ESQL\Bridge\ApiPlatform\State\Provider; use Soyuka\ESQL\Bridge\ApiPlatform\State\Processor; /** * #[ApiResource(provider: Provider::class, processor: Processor::class)] */ class Car {}
This will automatically enable the use of:
- a
CollectionProvider
using raw SQL. - an
ItemProvider
using raw SQL. - compose-able filters built using Postgrest specification
- a powerful sort extension also following Postgrest specification
- our own
DataPaginator
that you can extend to your will
You can find examples of Sorting and Filtering.
FAQ
Wait did you just re-create DQL?
No. This library offers shortcuts to redundant operations when writing SQL using Doctrine's metadata. We still benefit from Doctrine's metadata and you can still use it to manage your Schema or fixtures.
What about Eloquent or another ORM?
It's planned to add support for Eloquent or other ORM systems once the API is stable enough.
Which Database Management Systems are supported?
With this library you write native SQL. All our helpers will output strings that are useable in the standard SQL specification and therefore should be supported by every relational DBMSusing SQL. The API Platform bridge is tested with SQLite and Postgres. It's only a matter of time to add tests for MariaDB and Mysql.
Are there any limitations or caveats?
You'll still write SQL so I guess not? The only thing noticeable is that binded parameters will take the name of the fields prefixed by :
. For example identifier()
will output alias.identifier_column = :identifier_fieldname
. Our FilterParser
uses unique parameters names.
What is the Mapper all about?
The Mapper maps arrays received via the PHP Data Objects (PDO) statement to plain PHP objects also known as Entities. This is why Object Relation Mapping is all about. Internally we're using JanePHP's automapper or Symfony's serializer.
What about writes on the API Platform bridge?
Write support, extended to how Doctrine does is is rather complex especially if you want to support embed writes (write relation at the same time as the main entity). It is possible but there's not much benefits in adding this on our bridge. However you can use some of our helpers to do updates and inserts.
A simple update:
<?php $data = new Car(); $car = $esql($data); $binding = $this->automapper->map($data, 'array'); // map your object to an array somehow $query = <<<SQL UPDATE {$car->table()} SET {$car->predicates()} WHERE {$car->identifier()} SQL; $connection->beginTransaction(); $stmt = $connection->prepare($query); $stmt->execute($binding); $connection->commit();
Same goes for inserting value:
<?php $data = new Car(); $binding = $this->automapper->map($data, 'array'); // map your object to an array somehow $car = $esql($data) $query = <<<SQL INSERT INTO {$car->table()} ({$car->columns()}) VALUES ({$car->parameters($binding)}); SQL; $connection->beginTransaction(); $stmt = $connection->prepare($query); $stmt->execute($binding); $connection->commit();
Note that if you used a sequence you'd need to handle that yourself.
Documentation
Doctrine
An ESQL instance offers a few methods to help you write SQL with the help of Doctrine's metadata. To ease there use inside HEREDOC calling __invoke($classOrObject)
on the ESQL
class will return an array with the following closure:
<?php use Soyuka\ESQL\Bridge\Doctrine\ESQL; use App\Entity\Car; // Doctrine's ManagerRegistry and an ESQLMapperInterface (see below) $esql = new ESQL($managerRegistry, $mapper); $car = $esql(Car::class); // the Table name // outputs "car" echo $car->table(); // the sql alias // outputs "car" echo $car->alias(); // Get columns: columns(?array $fields = null, string $output = $car::AS_STRING): string // columns() outputs "car.id, car.name, car.model_id" // output can also take: $car::AS_ARRAY | $car::WITHOUT_ALIASES | $car::WITHOUT_JOIN_COLUMNS | $car::IDENTIFIERS echo $car->columns(); // Get a single column: column(string $fieldName): string // column('id') outputs "car.id" echo $car->column('id'); // Get an identifier predicate: identifier(): string // identifier() outputs "car.id = :id" echo $car->identifier(); // Get a join predicate: join(string $relationClass): string // join(Model::class) outputs "car.model_id = model.id" echo $car->join(Model::class); // All kinds of predicates: predicates(?array $fields = null, string $glue = ', '): string // predicates() outputs "car.id = :id, car.name = :name" echo $car->predicates();
More advanced utilities are available as:
<?php // Get a normalized value for SQL, sometimes booleans are in fact integer: toSQLValue(string $fieldName, $value) // toSQLValue('sold', true) output "1" on sqlite but "true" on postgresql $car->toSQLValue('sold', true); // Given an array of bindings, will output keys prefixed by `:`: parameters(array $bindings): string // parameters(['id' => 1, 'color' => 'blue']) will output ":id, :color" $car->parameters();
This are useful to build filters, write systems or even a custom mapper.
ESQL works using aliases and mapping them to classes and their properties. When working on relation you'll have to use:
<?php $car = $esql(Car::class); $car->alias(); // car $model = $car(Model::class); $model->alias(); // car_model
This way, ESQL knows to map the Model
to the Car->model
property. When working with DTOs the relation may not be found and you can alias the relation yourself:
<?php // Let's compute statistics and map car properties to the Aggregate class // The entity used is Car, mapped to Aggregate and using an SQL alias "car" $car = $esql(Car::class, Aggregate::class, 'car'); // The model relation doesn't exist, let's just use the model property $model = $car(Model::class, 'model');
The full interface is available as ESQLInterface.
Mapping
Automapper
The ESQLMapper
transforms an array retrieved via the PDOStatement fetch
or fetchAll
methods to the corresponding PHP Objects.
<?php // AutoMapper is an instance of JanePHP's automapper (https://github.com/janephp/automapper) $mapper = new ESQLMapper($autoMapper, $managerRegistry); $model = new Model(); $model->id = 1; $model->name = 'Volkswagen'; $car = new Car(); $car->id = 1; $car->name = 'Caddy'; $car->model = $model; $car2 = new Car(); $car2->id = 2; $car2->name = 'Passat'; $car2->model = $model; // Aliases should be generated by ESQL to map properties and relation properly $this->assertEquals([$car, $car2], $mapper->map([ ['car_id' => '1', 'car_name' => 'Caddy', 'model_id' => '1', 'model_name' => 'Volkswagen'], ['car_id' => '2', 'car_name' => 'Passat', 'model_id' => '1', 'model_name' => 'Volkswagen'], ], Car::class));
There's also a Mapper built with the symfony/serializer
.
Bundle configuration
esql: mapper: Soyuka\ESQL\Bridge\Automapper\ESQLMapper api-platform: enabled: true
Paginator
API Platform has great defaults for pagination. Using Soyuka\ESQL\Bridge\ApiPlatform\DataProvider\DataPaginator
, fetching data would look like this:
<?php $esql = $this->esql->__invoke(Car::class); $parameters = []; $query = <<<SQL SELECT {$esql->columns()} FROM {$esql->table()} SQL; if ($paginator = $this->dataPaginator->getPaginator($resourceClass, $operationName)) { return $paginator($esql, $query, $parameters, $context); }
If you want to handle the pagination yourself, we provide a way to do so:
<?php $resourceClass = Car::class; $operationName = 'get'; $esql = $this->esql->__invoke($resourceClass); ['itemsPerPage' => $itemsPerPage, 'firstResult' => $firstResult, 'nextResult' => $nextResult, 'page' => $page, 'partial' => $isPartialEnabled] = $this->dataPaginator->getPaginationOptions($resourceClass, $operationName); $query = <<<SQL SELECT {$esql->columns()} FROM {$esql->table()} LIMIT $itemsPerPage OFFSET $firstResult SQL; // fetch data somehow and map $data = $esql->map($data); $countQuery = <<< SQL SELECT COUNT(1) as count FROM {$esql->table()} SQL; // get count results somehow $count = $countResult['count']; return $isPartialEnabled ? new PartialPaginator($data, $page, $itemsPerPage) : new Paginator($data, $page, $itemsPerPage, $count);