Download the PHP package soyuka/esql without Composer

PHP Extended SQL

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.

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:

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 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:

Same goes for inserting value:

Note that if you used a sequence you'd need to handle that yourself.

Documentation

• Doctrine
• Mapping
• Bundle configuration
• Paginator
• Examples

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:

More advanced utilities are available as:

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:

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:

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.

There's also a Mapper built with the symfony/serializer.

Bundle configuration

Paginator

API Platform has great defaults for pagination. Using Soyuka\ESQL\Bridge\ApiPlatform\DataProvider\DataPaginator, fetching data would look like this:

If you want to handle the pagination yourself, we provide a way to do so:

Examples

• Aggregates
• Product with CTE