- Notifications
You must be signed in to change notification settings - Fork4
soyuka/esql
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
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.
<?phpuseApp\Entity\Car;useApp\Entity\Model;useSoyuka\ESQL\Bridge\Doctrine\ESQL;useSoyuka\ESQL\Bridge\Automapper\ESQLMapper;$connection =$managerRegistry->getConnection();$mapper =newESQLMapper($autoMapper,$managerRegistry);$esql =newESQL($managerRegistry,$mapper);$car =$esql(Car::class);$model =$car(Model::class);$query =<<<SQLSELECT{$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 orread this blog article to see it in action.
This package comes with an API Platform bridge that supports filters and pagination. To use our bridge, use theesql attribute:
<?phpuseApiPlatform\Metadata\ApiResource;useSoyuka\ESQL\Bridge\ApiPlatform\State\Provider;useSoyuka\ESQL\Bridge\ApiPlatform\State\Processor;/** * #[ApiResource(provider: Provider::class, processor: Processor::class)] */class Car {}
This will automatically enable the use of:
- a
CollectionProviderusing raw SQL. - an
ItemProviderusing raw SQL. - compose-able filters built usingPostgrest specification
- a powerful sort extension also followingPostgrest specification
- our own
DataPaginatorthat you can extend to your will
You can find examples ofSorting andFiltering.
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.
It's planned to add support for Eloquent or other ORM systems once the API is stable enough.
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.
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 exampleidentifier() will outputalias.identifier_column = :identifier_fieldname. OurFilterParser uses unique parameters names.
The Mapper maps arrays received via thePHP Data Objects (PDO) statement to plain PHP objects also known as Entities. This is why Object Relation Mapping is all about. Internally we're usingJanePHP's automapper or Symfony's serializer.
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 =newCar();$car =$esql($data);$binding =$this->automapper->map($data,'array');// map your object to an array somehow$query =<<<SQLUPDATE{$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 =newCar();$binding =$this->automapper->map($data,'array');// map your object to an array somehow$car =$esql($data)$query =<<<SQLINSERT 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.
An ESQL instance offers a few methods to help you write SQL with the help of Doctrine's metadata. To ease there use insideHEREDOC calling__invoke($classOrObject) on theESQL class will return an array with the following closure:
<?phpuseSoyuka\ESQL\Bridge\Doctrine\ESQL;useApp\Entity\Car;// Doctrine's ManagerRegistry and an ESQLMapperInterface (see below)$esql =newESQL($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::IDENTIFIERSecho$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 theModel to theCar->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 asESQLInterface.
TheESQLMapper transforms an array retrieved via the PDOStatementfetch orfetchAll methods to the corresponding PHP Objects.
<?php// AutoMapper is an instance of JanePHP's automapper (https://github.com/janephp/automapper)$mapper =newESQLMapper($autoMapper,$managerRegistry);$model =newModel();$model->id =1;$model->name ='Volkswagen';$car =newCar();$car->id =1;$car->name ='Caddy';$car->model =$model;$car2 =newCar();$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 thesymfony/serializer.
esql:mapper:Soyuka\ESQL\Bridge\Automapper\ESQLMapperapi-platform:enabled:true
API Platform has great defaults for pagination. UsingSoyuka\ESQL\Bridge\ApiPlatform\DataProvider\DataPaginator, fetching data would look like this:
<?php$esql =$this->esql->__invoke(Car::class);$parameters = [];$query =<<<SQLSELECT{$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 =<<<SQLSELECT{$esql->columns()} FROM{$esql->table()}LIMIT$itemsPerPage OFFSET$firstResultSQL;// fetch data somehow and map$data =$esql->map($data);$countQuery =<<< SQLSELECT COUNT(1) as count FROM{$esql->table()}SQL;// get count results somehow$count =$countResult['count'];return$isPartialEnabled ?newPartialPaginator($data,$page,$itemsPerPage) :newPaginator($data,$page,$itemsPerPage,$count);
About
PHP Extended SQL