Le blog

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.

#
Le cas d’usage 

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.

#
Mise en pratique

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"
  }
}
#
Comprendre la magie derrière Doctrine et API Platform

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 :

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

Doctrine profiler performance8 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 ?

#
Améliorer l’extraction de données

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 c

Sur 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.

#
ESQL comme alternative 

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 Product

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

doctrine queries with esql

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 : 

ESQL queries reduced to 3#
Filtre sur les catégories

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.

Le blog

Pour aller plus loin