Comment réaliser de belles requêtes SQL avec Doctrine

Doctrine est un ORM populaire offrant de nombreuses possibilités mais pas toujours bien mises en valeur. Cet article a pour but de montrer plusieurs manières de concevoir ses requêtes SQL.

Chez Wanadev, nous avons pour habitude d'utiliser l'ORM Doctrine avec nos projets Symfony.

Doctrine est depuis la version 2 du Frawework Full Stack l'ORM par défaut. Malgré toutes les critiques émises contre lui (performance, consommation mémoire, complexité…), on constate que nous disposons aujourd'hui de peu d'alternatives.

La seule proposant peu ou prou les mêmes outils est Propel mais encore faudrait-il que cette solution s'intègre aussi bien que son concurrent.

En dépit des performances médiocres décrites dans de nombreux articles faits par la communauté, ce qui nous gêne le plus reste la flexibilité d'utilisation. Comprenez, je veux faire une requête mais il est très difficile (voire impossible) de la traduire en DQL (Doctrine Query Language).

Cela s'explique par le choix de Doctrine d'être « compatible » avec un maximum de base de données (MySQL/MariaDB, Oracle, SQL Server, PostgreSQL, SQLite pour les plus connues) et assure un dénominateur commun entre toutes ses plateformes.

Les « fonctions primaires » qui sont disponibles dans le vendor sont :

• AND
• FROM
• GROUP BY
• INNER JOIN
• LEFT JOIN
• OR
• ORDER BY
• SELECT

De cette même manière, les opérateurs conditionnels disponibles sont :

• =
• <> ou !=
• <
• <=
• >
• >=

Les deux listes précédentes ne représentent pas toutes les fonctionnalités disponibles sur chacune des bases de données supportées par Doctrine. Quelques exemples des fonctionnalités proposées par les principaux SGBD :

• Fonctions disponibles sur MySQL
• Fonctions disponibles sur MariaDB
• Fonctions disponibles sur PostgreSQL
• Fonctions disponibles sur SQLite

Nous nous sommes souvent retrouvés devant l'impossibilité de faire une requête un peu costaud avec l'ORM, et nos solutions étaient de découper une requête SQL en plusieurs requêtes, de réaliser un traitement en PHP ou carrément de rebrousser chemin… Une hérésie pour nous, mais également une perte de performance non négligeable !

Devant ce problème levé par toute la communauté, certaines solutions comme Pomm, un OMM pour PostgreSQL ont émergé.

Pomm est un très bon exemple car PostgreSQL propose un nombre impressionnant de fonctionnalités et l'utilisation de l'ORM de Doctrine compromet fortement la plus-value à jouer avec un tel SGBD. Dans le cas du développement du projet du CREN, la fonction qui nous a le plus manqué était ILIKE.

Voyons maintenant toutes les manières de requêter vos SGBD.

Requête classique

$user = …;
$category = …;

$em = $this->container->get("doctrine.orm.default_entity_manager");
$entities = $em->getRepository(MyClass::class)->findBy([
    "user" => $user,
    "category" => $category,
]);

Dans ce cas, nous avons recherché un objet de type MyClass ayant pour attributs user et category les valeurs citées.

L'EntityRepository propose par défaut quelques méthodes pour vous éviter de les écrire.

• find prend un unique paramètre et recherche l'argument dans la clé primaire de l'entité.
• findBy prend 4 paramètres ($criteria, $orderBy, $limit, $offset). Cette méthode retourne des résultats correspondant aux valeurs des clés demandées.
• findAll est un alias de findBy([]). Il retourne par conséquent tous les résultats.
• findOneBy fonctionne comme la méthode findBy mais retourne un unique résultat et non pas un tableau.

La méthode __call étant implémentée dans les EntityRepository, sachez que si vous appelez findByUser, que cette méthode n'a pas été définie dans votre repository et que vous disposez d'un champ user dans votre entité, vous effectuerez une recherche sur ce même champ uniquement.

^ Je vous déconseille l'utilisation de ces alias car ce sont des raccourcis. Ces méthodes peuvent être définies dans vos repository class et changer le comportement de votre application. Personnellement j'aime le code et les comportements explicites.

Écrire correctement une requête DQL avec Symfony (part. 1)

// src/AppBundle/Repository/MyClassRepository.php

<?php

namespace AppBundle\Repository;

use Doctrine\ORM\EntityRepository;

class MyClassRepository extends EntityRepository
{
    public function findItemsCreatedBetweenTwoDates(\DateTime $beginDate, \DateTime $endDate)
    {
        return $this->createQueryBuilder('m')
                    ->where("m.createdAt > ?1")
                    ->andWhere("m.createdAt < ?2")
                    ->setParameter(1, $beginDate)
                    ->setParameter(2, $endDate)
                    ->getQuery()
                    ->getResult();
    }
}

De cette manière on retourne des éléments créés entre deux dates. Il n'existe pas de méthodes pré-définies permettant de réaliser cette requête. Cette méthode est bien car facile et elle a le mérite d'automatiser la conversion des types. Dans notre cas, un objet DateTime est automatiquement formaté pour correspondre à notre base de données (object -> '2016-05-16 12:10:00').

Écrire une requête DQL avec Symfony (part. 2)

Dans l'exemple précédent, on montre comment définir des conditions les unes à la suite des autres. Cela permet de requêter de nombreux cas... mais pas tous. Voici comment faire pour effectuer des requêtes avec des sous-conditions.

// src/AppBundle/Repository/MyClassRepository.php

use Doctrine\ORM\Query\Expr;

public function findItemsCreatedBeforeDateAndNoDeleted(\DateTime $createdBefore)
{
    return $this->createQueryBuilder('m')
                ->where("m.createdAt < ?1")
                ->andWhere(
                    new Expr\Orx([
                        "m.deletedAt IS NULL",
                        "m.deletedAt > ?2",
                    ])
                )
                ->setParameter(1, $createdBefore)
                ->setParameter(2, new \DateTime())
                ->getQuery()
                ->getResult();
}

Si on traduit cette requête, voici ce qui sera exécuté :

SELECT m.* FROM myclass AS m WHERE m.createdAt < '2016-05-16 11:00:00' AND (m.deletedAt IS NULL OR m.deletedAt > '2016-05-16 12:00:00');

Écrire une requête DQL avec Symfony (part. 3)

Lorsque nous voulons écrire une requête simple en DQL, on peut rapidement être confronté à un problème : une fonction non disponible dans Doctrine. Ce problème se produit assez souvent dans la mesure où comme je le disais plus haut, la liste des fonctions supportées par défaut est assez limitée.

Pour se faire, nous allons ajouter à Doctrine une classe permettant d'expliquer le fonctionnement de la méthode.

Prenons l'exemple de la méthode MD5 :

// src/AppBundle/DQL/MD5Function.php

<?php

namespace AppBundle\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class MD5Function extends FunctionNode
{
    public $value;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->value = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'MD5(' . $this->value->dispatch($sqlWalker) . ')';
    }
}

# app/config/config.yml

doctrine:
    orm:
        dql:
            string_functions:
                MD5: AppBundle\DQL\MD5Function

En ajoutant cette méthode à Doctrine, on peut ensuite l'utiliser dans une DQL et on évite de réaliser certains traitements PHP ou en RAW SQL.

Écrire une requête SQL complexe avec Doctrine, en RAW SQL : level-up -1 !

Lorsque nos besoins dépassent les solutions proposées par l'ORM, nous étions contraints d'utiliser des requêtes SQL en évitant d'utiliser l'ORM, ce que nous appelons « RAW SQL ». Cette manière de faire est souvent utilisée alors que nous avons d'autres possibilités (ce que nous verrons dans le paragraphe suivant) mais il peut arriver que le résultat attendu ne corresponde pas au mapping d'une entité. On attend alors un tableau et non pas un objet.

On envoie alors la requête au DBAL qui gère les connections aux bases de données. Voici comment exécuter une requête SQL sans passer par l'ORM :

// src/AppBundle/Repository/MyClassRepository.php

public function getCustomInformations()
{
    $rawSql = "SELECT m.id, (SELECT COUNT(i.id) FROM item AS i WHERE i.myclass_id = m.id) AS total FROM myclass AS m";

    $stmt = $this->getEntityManager()->getConnection()->prepare($rawSql);
    $stmt->execute([]);

    return $stmt->fetchAll();
}

Écrire une requête SQL et obtenir des objets mappés

Cette méthode est peu décrite et pourtant elle est extrêmement simple à mettre en place. On effectue une requête SQL (RAW SQL) que l'on exécute comme une NQL (Native Query Language) et on lui associe un objet de type ResultSetMapping pour re-mapper le tableau de valeurs dans un objet.

La requête SQL exécutée utilise un tri spécifique, voici la documentation pour comprendre la requête exécutée.

// src/AppBundle/Repository/MyClassRepository.php

use Doctrine\ORM\Query\ResultSetMappingBuilder;

public function findItemsAndSortByStatus()
{
    // la table en base de données correspondant à l'entité liée au repository en cours
    $table = $this->getClassMetadata()->table["name"];

    // Dans mon cas je voulais trier mes résultats avec un ordre bien particulier
    $sql =  "SELECT m.* "
            ."FROM ".$table." AS m "
            ."WHERE (m.deleted_at IS NULL OR m.deleted_at > :current_time) "
            ."ORDER BY m.status = :status_available DESC, m.status = :status_unknown DESC, m.status = :status_unavailable DESC, m.priority ASC";

    $rsm = new ResultSetMappingBuilder($this->getEntityManager());
    $rsm->addEntityResult(MyClass::class, "m");

    // On mappe le nom de chaque colonne en base de données sur les attributs de nos entités
    foreach ($this->getClassMetadata()->fieldMappings as $obj) {
        $rsm->addFieldResult("m", $obj["columnName"], $obj["fieldName"]);
    }

    $stmt = $this->getEntityManager()->createNativeQuery($sql, $rsm);

    $stmt->setParameter(":current_time", new \DateTime("now"));
    $stmt->setParameter(":status_available", MyClass::STATUS_AVAILABLE);
    $stmt->setParameter(":status_unknown", MyClass::STATUS_UNKNOWN);
    $stmt->setParameter(":status_unavailable", MyClass::STATUS_UNAVAILABLE);

    $stmt->execute();

    return $stmt->getResult();
}

Cette méthode retourne une tableau d'objets (avec pour type MyClass). Il s'agit d'objets mappés. Nice ?

Petits bonus pour des fonctions DQL

Florent Viel propose sur son repository une panoplie de fonctions DQL pour MySQL

@agence_wanadev @baptistedonaux et pour pas se fatiguer on utilise https://t.co/0wiAAKni8F ???? pour les fonctions manquantes à DQL

— lǝᴉΛ ʇuǝɹolℲ (@florent_viel) 31 mai 2016

Conclusion

Il existe de nombreuses façons de requêter nos bases de données et une multitude de solutions pour parvenir à nos fins.

Réaliser des traitements PHP représente une perte de performance considérable dans la mesure où les SGBD sont là pour nous renvoyer des ensembles formatés comme nous le souhaitons. Améliorer les performances de nos applications peut passer par de nombreuses optimisations comme l'ajout de cache (solution extrême) ou par la réduction du nombre de requêtes exécutées.

Cet article vise à montrer les solutions à notre disposition lorsque nous utilisons Doctrine, l'ORM le plus populaire dans la sphère de Symfony.