ESQL, une alternative au DQL
Publié le 05 février 2021
Dans cet article, nous allons vous présenter la bibliothèque ESQL avec un cas d'utilisation complexe, illustrant les raisons qui ont motivé sa création.
Nous avons une collection de produits appartenant chacun à une catégorie. Notre API utilise le vocabulaire de https://schema.org/Product pour représenter notre produit. La catégorie de ce produit est définie par : "une catégorie pour l'article. Des signes "supérieurs à" ou des slashes peuvent être utilisés pour indiquer de manière informelle une hiérarchie de catégories.".
Nous utilisons API Platform pour la pagination, les filtres ainsi que pour le support du format JSON-LD, complété par la spécification Hydra.
Dans un souci de lisibilité, nous avons supprimé les parties non essentielles du code. Il est disponible sur Github avec des instructions pour le lancer vous-même. Créons deux entités Doctrine avec d'abord la catégorie :
<?php
namespace AppEntity;
use\ ApiPlatformCoreAnnotationApiProperty;
use\ ApiPlatformCoreAnnotationApiResource;
use\ DoctrineCommonCollectionsCollection;
use\ DoctrineORMMapping as ORM;
/**
* @ApiResource(iri="http://schema.org/Category")
* @ORM\Entity
*/
class Category
{
/**
* @ORM\Column(type="string", length=255)
*/
public string $name;
/**
* @ORM\Column(type="string", length=30)
* @ORM\Id
* @ApiProperty(iri="http://schema.org/identifier", identifier=true)
*/
public string $identifier;
/**
* @ORM\OneToMany(targetEntity="Category", mappedBy="parent")
* @ApiProperty(readable=false)
*/
private Collection $children;
/**
* @ORM\ManyToOne(targetEntity="Category", inversedBy="children")
* @ORM\JoinColumn(name="parent_id", referencedColumnName="identifier")
*/
public ?Category $parent = null;
}La catégorie a un identifiant, un nom et utilise une relation OneToMany pour la hiérarchie. Maintenant, pour le produit, nous allons nommer notre relation à la catégorie categoryRelation parce que nous voulons qu’elle soit affichée avec des "slashes (...) pour indiquer de manière informelle une hiérarchie de catégories" comme le propose le schéma JSON. Comme cette entité est également notre représentation (JS)Object(N) sur l'API, nous définirons un champ qui n'est pas mappé à Doctrine et un getter qui construit et affiche la hiérarchie des catégories :
<?php
namespace AppEntity;
class Product
{
/**
* @ApiProperty(iri="http://schema.org/category")
*/
private string $category = '';
public function getCategory(): string
{
$category = $this->categoryRelation;
$str = $category->name;
while ($category = $category->parent) {
$str = $category->name . ' / ' . $str;
}
return $str;
}
}Voici l’entité Product :
<?php
namespace AppEntity;
use\ ApiPlatformCoreAnnotationApiProperty;
use\ ApiPlatformCoreAnnotationApiResource;
use\ DoctrineORMMapping as ORM;
use\ SymfonyBridgeDoctrineIdGeneratorUlidGenerator;
use\ SymfonyComponentUidUlid;
/**
* @ApiResource(iri="http://schema.org/Product")
* @ORM\Entity
*/
class Product
{
/**
* @ORM\Id
* @ORM\Column(type="ulid", unique=true)
* @ORM\GeneratedValue(strategy="CUSTOM")
* @ORM\CustomIdGenerator(class=UlidGenerator::class)
*/
private Ulid $id;
/**
* @ORM\Column(type="string", length=255)
* @ApiProperty(iri="http://schema.org/name")
*/
public string $name;
/**
* @ORM\Column(type="string", length=255)
* @ApiProperty(iri="http://schema.org/description")
*/
public string $description;
/**
* @ORM\ManyToOne(targetEntity=Category::class)
* @ORM\JoinColumn(name="category_id", referencedColumnName="identifier")
* @ApiProperty(readable=false)
*/
public Category $categoryRelation;
/**
* @ApiProperty(iri="http://schema.org/category")
*/
private string $category = '';
/**
* @ApiProperty(iri="https://schema.org/gtin")
* @ORM\Column(type="string", length=14)
*/
public string $gtin;
public function getCategory(): string
{
$category = $this->categoryRelation;
$str = $category->name;
while ($category = $category->parent) {
$str = $category->name . ' / ' . $str;
}
return $str;
}
}
Le champ GTIN signifie Global Trade Item Number et représente souvent le code barres du produit. Maintenant que nous avons nos deux entités, nous allons ajouter quelques données de tests et exécuter notre API. Pour cela, nous allons utiliser AliceBundle :
AppEntityProduct:
product_{1..100}:
gtin: '<ean13()>'
name: '<name()>'
description: '<realText(140)>'
categoryRelation: '@<randomElement([vegetables_specified,bagged_salads,herbs_aromatic,fresh_fruits,dried_fruits,iceberg])>'Le fichier complet est disponible sur Github. Après le chargement des fixtures (bin/console hautelook:fixtures:load), lancez l'API des produits :
{
"@context": "/api/contexts/Product",
"@id": "/api/products",
"@type": "hydra:Collection",
"hydra:member": [
{
"@id": "/api/products/01774d7b-a79e-a138-6ff2-e94806f06837",
"@type": "http://schema.org/Product",
"name": "Tianna Ziemann",
"description": "Knave was standing before them, in chains, with a round face, and was gone across to the croquet-ground. The other guests had taken his.",
"gtin": "5794390900075",
"id": "01774d7b-a79e-a138-6ff2-e94806f06837",
"category": "Vegetables / Bagged salads / Iceberg"
},
{
"@id": "/api/products/01774d7b-a79e-a138-6ff2-e94806f06838",
"@type": "http://schema.org/Product",
"name": "Dr. Samir Weimann DDS",
"description": "Alice knew it was over at last: and I do wonder what I eat is the capital of Paris, and Paris is the reason and all her wonderful. ",
"gtin": "4173048045459",
"id": "01774d7b-a79e-a138-6ff2-e94806f06838",
"category": "Vegetables / Herbs and Aromatic Plants"
},
{
"@id": "/api/products/01774d7b-a79e-a138-6ff2-e94806f06839",
"@type": "http://schema.org/Product",
"name": "Anais Lockman IV",
"description": "Alice did not feel encouraged to ask his neighbour to tell me who YOU are, first.' 'Why?' said the King said to Alice. 'What IS the same.",
"gtin": "7892793312223",
"id": "01774d7b-a79e-a138-6ff2-e94806f06839",
"category": "Fruits / Dried Fruits"
},.
],
"hydra:totalItems": 100,
"hydra:view": {
"@id": "/api/products.jsonld?perPage=10&page=1",
"@type": "hydra:PartialCollectionView",
"hydra:first": "/api/products.jsonld?perPage=10&page=1",
"hydra:last": "/api/products.jsonld?perPage=10&page=10",
"hydra:next": "/api/products.jsonld?perPage=10&page=2"
}
}Plusieurs choses se font lorsque vous exécutez la requête /api/products.jsonld :
- Le kernel HTTP de Symfony traite la demande
- API Platform reçoit l’information que vous recherchez une collection de produits
- Doctrine va chercher la collection et hydrate la classe Product
- API Platform va sérialiser les données en utilisant le serializer de Symfony.
Aucun de ces acteurs n'a obtenu les informations sur la ressource Category. Rappelez-vous que nous exécutons le code suivant lorsque nous récupérons la catégorie d’un produit :
<?php
public function getCategory(): string
{
$category = $this->categoryRelation;
$str = $category->name;
while ($category = $category->parent) {
$str = $category->name . ' / ' . $str;
}
return $str;
}Ce code est appelé pendant la phase de sérialisation. C'est la phase où l'objet Product se transforme en JSON. À ce moment-là, la seule information connue par Doctrine est l'identifiant categoryRelation. Lorsque vous appelez $category->name, si Doctrine n'a pas cet objet en mémoire, il le récupère dans la base de données. Pour illustrer cela, regardons le profiler de Symfony, depuis l'onglet Performance :
Dans cet exemple, nous avons appelé api/products.jsonld?perPage=100 qui va aller chercher tous les produits. La conséquence ici c’est que Doctrine va créer 8 requêtes supplémentaires, une pour chaque catégorie. Nous pouvons observer ceci depuis l’onglet Doctrine du profiler :
8 requêtes de recherche de catégories, la requête de collection de produits et une requête count utilisée pour la pagination. Ce comportement est fonctionnel et a même des performances acceptables mais que se passerait-il si, au lieu de n'avoir que 8 catégories, nous parlions de hiérarchies complexes d'employés ou d'entreprises ayant des groupes et de nombreuses connexions ? Serait-ce toujours acceptable ?
Si vous connaissez SQL, vous avez probablement entendu parler de Common Table Expression (CTE). Il s'agit d'un ensemble de résultats temporaires nommés qui, entre autres choses, peuvent être récursifs. C’est donc très utile afin de créer des requêtes hiérarchiques. Par exemple, écrivons une requête de ce type pour récupérer toutes les catégories en utilisant un CTE nommé descendants :
WITH RECURSIVE
descendants(identifier, name, parent_id) AS (
SELECT c.identifier, c.name, c.parent_id FROM category c WHERE c.parent_id IS NULL
UNION ALL
SELECT c.identifier, c.name, c.parent_id FROM descendants, category c WHERE c.parent_id = descendants.identifier
)
SELECT c.identifier, c.name, c.parent_id FROM descendants cSur les bases de données Oracle, vous allez préférer utiliser l'expression CONNECT BY qui calcule une pseudo-colonne permettant de connaître le niveau en plus de gérer le modèle hiérarchique.
Cette requête nous aiderait à réduire le nombre précédent de 8 requêtes à une seule. Toutefois, API Platform fonctionne avec le langage de requête de doctrine (DQL) et, à notre connaissance, il n'existe pas d'implémentation pour les CTE. Nous avons trouvé un document de recherche et ce cte-builder qui peuvent vous aider à ajouter ce CTE avec Doctrine si vous le voulez vraiment, c'est tout à fait possible. Nous pourrions utiliser du SQL natif avec Doctrine et cela nécessiterait l'utilisation d'un ResultSetMapping, la classe qui se trouve derrière le mappage de Doctrine entre les données récupérées (les tableaux) et les classes PHP. Malgré cela, nous perdrions les filtres et la pagination d'API Platform car ils fonctionnent également avec le langage de requête de Doctrine.
Une autre possibilité avec Doctrine, est d’utiliser un Nested Set, en utilisant par exemple l’extension Doctrine Tree.
La bibliothèque ESQL offre un bridge API Platform qui s'intègre parfaitement à votre stack actuelle. Ajoutons la bibliothèque au projet :
composer require soyuka/esql jane-php/automapper
Chargez les bundles (nous utilisons l'AutoMapper de JanePHP pour l'exemple, un mappeur utilisant le sérialiseur de Symfony est également disponible) :
<?php
# config/bundles.php
return [
// ...
JaneAutoMapperBundleJaneAutoMapperBundle::class => ['all' => true],
SoyukaESQLBridgeSymfonyBundleESQLBundle::class => ['all' => true],
];Ensuite, utilisons le bridge en utilisant l'attribut esql :
<?php
/**
* @ApiResource(iri="http://schema.org/Product", attributes={"esql"=true})
* @ORM\Entity
*/
class ProductRechargez /api/products.jsonld et vous devriez tomber sur une erreur :
The property "AppEntityCategory::$name" is not readable because it is typed "string". You should initialize it or declare a default value instead.
C'est normal, vérifiez à nouveau l'onglet Doctrine du profiler. Nous avons exécuté deux requêtes mais n'avons pas récupéré les catégories :
Nous les ajouterons nous-mêmes en ajoutant un fournisseur de données. Dans API Platform, un DataProvider est le principal point d'extension lorsqu'il s'agit d'extraire des données. Notre ProductDataProvider se chargera donc :
- de récupérer les catégories à l'aide d'un CTE
- d'utiliser la composition en décorant le DataProvider fourni par ESQL
La déclaration de service est la suivante :
AppDataProviderProductDataProvider:
tags:
- { name: 'api_platform.collection_data_provider', priority: 20 }
arguments:
$decorated: '@esql.api_platform.default.collection_data_provider'Et voici le ProductDataProvider (regardez les commentaires) :
<?php
namespace AppDataProvider;
use\ ApiPlatformCoreDataProviderCollectionDataProviderInterface;
use\ ApiPlatformCoreDataProviderContextAwareCollectionDataProviderInterface;
use\ ApiPlatformCoreDataProviderRestrictedDataProviderInterface;
use\ DoctrinePersistenceManagerRegistry;
use\ SoyukaESQLESQL;
use\ SoyukaESQLESQLInterface;
use\ SoyukaESQLESQLMapperInterface;
use\ AppEntityCategory;
use\ AppEntityProduct;
use\ SymfonyComponentHttpFoundationRequestStack;
use\ SymfonyComponentHttpKernelExceptionBadRequestHttpException;
final class ProductDataProvider implements RestrictedDataProviderInterface, CollectionDataProviderInterface, ContextAwareCollectionDataProviderInterface
{
private RequestStack $requestStack;
private ManagerRegistry $managerRegistry;
private ESQLMapperInterface $mapper;
private ESQLInterface $esql;
private ContextAwareCollectionDataProviderInterface $decorated;
public function __construct(RequestStack $requestStack, ManagerRegistry $managerRegistry, ESQLMapperInterface $mapper, ESQLInterface $esql, ContextAwareCollectionDataProviderInterface $decorated)
{
$this->requestStack = $requestStack;
$this->managerRegistry = $managerRegistry;
$this->mapper = $mapper;
$this->esql = $esql;
$this->decorated = $decorated;
}
public function supports(string $resourceClass, string $operationName = null, array $context = []): bool
{
return Product::class === $resourceClass;
}
public function getCollection(string $resourceClass, string $operationName = null, array $context = [])
{
// Call the DataProvider to retrieve products
$data = $this->decorated->getCollection($resourceClass, $operationName, $context);
// Fetch categories
$categories = $this->getCategories(); // Map categories to the product ourselves
foreach ($data as $product) {
foreach ($categories as $category) {
if ($product->categoryRelation->identifier === $category->identifier) {
$product->categoryRelation = $category;
}
}
}
return $data;
}
private function getCategories(): array
{
$categoryParameter = null === ($request = $this->requestStack->getCurrentRequest()) ? null : $request->query->get('category');
if (is_array($categoryParameter)) {
throw new BadRequestHttpException();
}
$connection = $this->managerRegistry->getConnection();
// If a category is present we can load only the tree for this specific category
$categoryPredicate = $categoryParameter ? 'c.identifier = :category' : 'c.parent_id IS NULL';
$category = $this->esql->__invoke(Category::class);
$query = <<<SQL
WITH RECURSIVE
ancestors(identifier, name, parent_id) AS (
SELECT c.identifier, c.name, c.parent_id FROM category c WHERE {$categoryPredicate}
UNION ALL
SELECT c.identifier, c.name, c.parent_id FROM ancestors, category c WHERE c.identifier = ancestors.parent_id
),
descendants(identifier, name, parent_id) AS (
SELECT c.identifier, c.name, c.parent_id FROM category c WHERE {$categoryPredicate}
UNION ALL
SELECT c.identifier, c.name, c.parent_id FROM descendants, category c WHERE c.parent_id = descendants.identifier
)
SELECT {$category->columns()} FROM ancestors {$category->alias()}
UNION
SELECT {$category->columns()} FROM descendants {$category->alias()}
SQL; // Use doctrine's executeQuery method, it calls prepare and execute
$stmt = $connection->executeQuery($query, $categoryParameter ? ['category' => $categoryParameter] : []);
$data = $stmt->fetchAll();
$categories = $this->mapper->map($data, Category::class); // We need to map categories to themselves to obtain a tree
foreach ($categories as $category) {
if (null === $category->parent) {
continue;
}
foreach ($categories as $parent) {
if ($parent->identifier === $category->parent->identifier) {
$category->parent = $parent;
}
}
}
return $categories;
}
}Et voilà ! Essayez d'appeler /api/products.jsonld?perPage=100, seules 3 requêtes seront donc exécutées :
Pour aller plus loin, nous allons maintenant ajouter un filtre personnalisé sur ce point de terminaison. La particularité de ce filtre est qu'il filtre sur l'ensemble de la hiérarchie. Ce qui signifie que :
- /api/products.jsonld?category=v renverra tous les produits appartenant aux catégories Vegetables et leurs sous-catégories.
- /api/products.jsonld?category=bagged_salads renverra tous les produits appartenant aux catégories bagged_salads.
Le bridge ESQL fournit une interface nommée QueryCollectionExtensionInterface qui vous permet de modifier la requête SQL originale et de l'adapter à vos besoins. Cela préserve la pagination et les filtres déjà disponibles.
<?php
namespace AppExtension;
use DoctrinePersistenceManagerRegistry;
use SoyukaESQLBridgeApiPlatformExtensionQueryCollectionExtensionInterface;
use SoyukaESQLESQLInterface;
use AppEntityCategory;
use AppEntityProduct;
use SymfonyComponentHttpFoundationRequestStack;
use SymfonyComponentHttpKernelExceptionBadRequestHttpException;
final class CategoryFilterExtension implements QueryCollectionExtensionInterface
{
private RequestStack $requestStack;
private ESQLInterface $esql;
private ManagerRegistry $managerRegistry;
public function __construct(RequestStack $requestStack, ESQLInterface $esql, ManagerRegistry $managerRegistry)
{
$this->requestStack = $requestStack;
$this->esql = $esql;
$this->managerRegistry = $managerRegistry;
}
public function apply(string $query, string $resourceClass, ?string $operationName = null, array $parameters = [], array $context = []): array
{
$request = $this->requestStack->getCurrentRequest(); // Just return early if the category parameter is not present
if (null === $request || !$request->query->has('category') || null === $categoryParameter = $request->query->get('category')) {
return [$query, $parameters];
}
if (is_array($categoryParameter)) {
throw new BadRequestHttpException();
}
$product = $this->esql->__invoke($resourceClass);
$category = $this->esql->__invoke(Category::class); // We change the query and use this one instead, it joins descendants over the products
$query = <<<SQL
WITH RECURSIVE
descendants(identifier, name, parent_id) AS (
SELECT c.identifier, c.name, c.parent_id FROM category c WHERE c.identifier = :category
UNION ALL
SELECT c.identifier, c.name, c.parent_id FROM descendants, category c WHERE c.parent_id = descendants.identifier
)
SELECT {$product->columns()} FROM {$product->table()}
JOIN descendants {$category->alias()} ON {$product->join(Category::class)}
SQL;
$parameters['category'] = $categoryParameter;
return [$query, $parameters];
}
public function supports(string $resourceClass, ?string $operationName = null, array $context = []): bool
{
return Product::class === $resourceClass;
}
}Voici la définition du service :
AppExtensionCategoryFilterExtension:
tags: ['esql.collection_extension']Et voilà, vous avez un filtre hiérarchique, avec des requêtes propres tout en bénéficiant de la pagination et des filtres. Par exemple, appeler l’URL /api/products.jsonld?category=bagged_salads&description=like.*looking* ajoutera une clause product.description LIKE %looking% !
Pour en savoir plus, consultez ce dépôt sur github ! Cet exemple avec ESQL est également disponible ici avec des instructions sur la façon de le faire fonctionner.
