Blog // Exirel.me

Boucle et SQL (et ORM) : la petite erreur à éviter

Par Florian Strzelecki - 18:51 - 11.04.2011

Tags : Programmation, Bonne pratique, PHP, Optimisation, ORM, SQL, Technique

Petite précaution avant d'entamer la lecture de cet article : il est technique, certes, mais ne concerne pas spécifiquement ni symfony ni doctrine. Le sujet de l'article est un problème technique très concret, qui se retrouve dans tous les langages, et avec n'importe quelle base de données.

En lisant le livre "Pratical symfony", plus spécifiquement le chapitre 6, je suis tombé sur un cas très classique : afficher une liste d'élément par catégorie, comme dans l'exemple qui suit.

L'approche ici - mais j'ai déjà vu ça ailleurs très souvent - est de récupérer la liste des catégories, puis, pour chaque catégorie, de récupérer la liste des offres de ladite catégorie.

Sauf qu'il y a un problème : voyez-vous lequel ?

Le noeud du problème

Le déroulement du script est on ne peut plus limpide :

  1. Récupération de la liste des catégories (via une requête SQL)
  2. Boucle sur cette liste de catégories
  3. Pour chaque élément, récupération de la liste des offres (via une requête SQL)
  4. Boucle sur cette liste d'offres

Cela semble plutôt logique, mais il y a un problème : s'il y a 2 catégories, cela fait donc 3 requêtes SQL exécutées (1 pour les catégories, et 1 par catégorie, soit 2 de plus). S'il y a 5, 10 ou 20 catégories, alors cela fait 6, 11 ou 21 requêtes SQL - dont toutes sauf 2 peuvent être empêchée.

C'est un problème très visible lorsque le développeur fait les requêtes "à la main" à chaque fois qu'il a besoin de faire appel à la base de données, mais c'est bien moins transparent lorsqu'un système d'ORM vient masquer ces appels.

Dans notre cas, c'est plutôt simple de s'en rendre compte : la clé étrangère est du côté des articles, et nous partons des catégories, donc nous savons qu'il va y avoir une requête supplémentaire effectuée à chaque passage de la boucle.

Mais c'est quelque chose de moins évident dans le cas où vous cherchez à obtenir la catégorie d'un article à partir de ce derniers : souvent, l'accesseur ou la propriété est directement disponible, et masque totalement le fait qu'à ce moment là une requête est effectuée(1).

Début de solution : le SQL à la main.

Pour éviter de n'avoir des requêtes SQL cachées par l'ORM, la solution la plus radicale est de ne pas utiliser l'ORM : mais ce n'est pas l'objectif. Après tout, l'ORM permet un tas de trucs, et son utilité n'est pas le sujet de l'article.

Cependant, ma méthode, pour trouver une solution à ce petit problème, a été de vérifier ce que je pouvais faire avec du simple SQL tout seul. Et voici ce que cela donne(2) :

    SELECT j.*
    FROM jobeet_job j
    INNER JOIN jobeet_category c ON j.category_id = c.id
    ORDER BY c.name, j.expires_at;

Le résultat de cette requête me donne la liste des offres triées par date d'expiration, mais surtout, en premier lieu, les ordonne par ordre alphabétique du nom de leur catégorie respective. L'ordre dans lequel je mets les instructions ORDER BY a un véritable impact sur ce que je souhaite faire, car il est primordial que les offres soient groupées par catégorie - sans pour autant obtenir toutes les données des catégories pour chaque ligne.

La solution pour l'affichage

Maintenant que j'ai une liste d'offres triées comme il faut (avec une seule requête SQL), je n'ai plus qu'à l'afficher. Tout d'abord, je récupère la liste des catégories (soit une requête SQL supplémentaire).

Cette liste de catégorie ne convient pas telle qu'elle : je dois d'abord créer un tableau dont chaque clé sera l'identifiant de la catégorie associée.

    $categories_par_id =
        array(2 => <objet catégorie id=2>,
            36 => <objet catégorie id=36>,
            8 => <objet catégorie id=8>);

Pour cela, j'effectue une boucle sur la liste des catégories, et je créé mon tableau comme j'en ai besoin.

Ensuite, je m'attaque à la vrai boucle, celle qui va permettre l'affichage des articles par catégorie ! Voici un exemple avec du code PHP minimaliste :

$display_cat_id = null;

foreach($job as $i => $job) {
    $cat_id = $job['categorie_id'];

// La catégorie a changé : affichage de l'entête de la catégorie
    if($cat_id != $display_cat_id) {
        // La catégorie précédente existait, ce n'est pas la première itération
        if(!is_null($display_cat_id)) {
        // Fermeture de la liste d'élément
            echo '</ul>';
        }

// Assignation de la nouvelle valeur de la catégorie à afficher
        $display_cat_id = $cat_id;

$categorie = $categories_par_id[$display_cat_id];

printf('<h3>Catégorie : %s</h3>', $categorie['name']);
    printf('<ul>');
    }

// Affichage de la ligne du job.
    printf('<li>%s (%s)</li>', $job['titre'], $job['expires_at']);
}

// Fermeture de la liste d'élément : cette fermeture ne peut se faire dans la boucle !
printf('</ul>');

C'est donc plutôt trivial, mais il fallait y penser : en triant les données avec SQL, je m'assure que les changements de catégorie ne se feront que dans l'ordre que j'ai choisi.

Quelque soit le nombre d'élément ou de catégorie, il n'y aura jamais que 2 requêtes SQL : avec 10 ou 50 catégories, avec 10 ou 1000 articles. Ce qui est quand même bien mieux !

La solution finale avec l'ORM (ici Doctrine)

Je parlais d'ORM au début, car c'est effectivement en utilisant un ORM que j'ai pu constater cette erreur. Par expérience, je sais d'ailleurs que c'est souvent avec un ORM que des erreurs d'optimisations peuvent survenir.

Ne vous y trompez pas : l'apparente "simplicité" des choses a un coût, et ce coût, vous pourriez avoir à le payer très cher un jour - ce qui serait dommage. Alors il faut toujours, et je dis bien toujours, comprendre ce que fait votre ORM lorsque vous voulez traiter des données.

En attendant, voici un début de solution que j'ai employée pour utiliser le principe vu plus haut avec l'ORM Doctrine :

    $q_j = Doctrine_Query::create()
        -> select('j.*')
        -> from('JobeetJob j')
        -> innerJoin('j.JobeetCategory c')
        -> addOrderBy('c.name')
        -> addOrderBy('j.expires_at DESC');

Et voici la requête que j'ai ainsi pu obtenir :

SELECT j.id AS j__id, j.category_id AS j__category_id,
    j.type AS j__type, j.company AS j__company,
    j.logo AS j__logo, j.url AS j__url, j.position AS j__position,
    j.location AS j__location, j.description AS j__description,
    j.how_to_apply AS j__how_to_apply, j.token AS j__token,
    j.is_public AS j__is_public, j.is_activated AS j__is_activated,
    j.email AS j__email, j.expires_at AS j__expires_at,
    j.created_at AS j__created_at, j.updated_at AS j__updated_at
FROM jobeet_job j
INNER JOIN jobeet_category j2 ON j.category_id = j2.id
ORDER BY j2.name, j.expires_at DESC

Vous pouvez la tester, c'est bien le résultat qui était attendu, avec succès ! Comme quoi, ce n'est pas si compliqué que ça...

(1): D'ailleurs, avec Django c'est un problème qui peut potentiellement être résolu en utilisant "select_related" mais pas que. Soyez vigilant.

(2) : Pour la lisibilité de la requête, je n'ai mis que "j.*" pour tout sélectionner, mais pour un vrai développement je n'hésite pas une seule seconde à lister les champs dont j'ai besoin.