Maîtriser le outer join comme un pro !

Il y a peu, j’ai eu l’occasion de m’occuper de l’optimisation d’une méthode qui chargeait plusieurs petits sets de données dans une table pour finalement renvoyer un seul paquet d’objets. Après avoir observé le code quelques secondes je me dis que tout ça peut être fait directement dans la base de données à condition de lui fournir la requête voulue !
Après un petit moment d’effort, j’ai fini par réussir mon coup à l’aide d’une fonctionnalité SQL qui m’avait toujours fait peur: le outer join !

Pour ceux qui ne sont pas à l’aise avec la syntaxe du code que j’utilise ici, je suis fan de la gem MetaWhere, et je ne sais plus vraiment écrire de ActiveRecord pur :)

L’Exemple

Prenons un petit exemple simple pour bien visualiser.

Imaginons une application avec des utilisateurs.
Ces utilisateurs ont besoin d’être validés par un administrateur pour se connecter au site.
Ils peuvent également payer pour différents abonnements qui eux aussi nécessitent la validation d’un administrateur pour être en règle.

Imaginons maintenant que vous vouliez une page de type TODO pour l’administrateur, avec la liste des utilisateurs en attente d’une validation et qu’il faut appeler.

Première solution (pas maline)

Bon … on va pas être bien malins pour le coup !

User.where(:validated => false).all
  + User.joins(:subscriptions)
        .where(:subscriptions => {:validated => false}).all

Simple, efficace. Deux chargements en base de données qu’on réuni en suite à l’aide d’une simple addition de tableau.

Deuxième solution (qui marche pas)

OK soyons plus malin maintenant. Finalement ce qu’on veut, c’est charger des utilisateurs ! On a qu’à faire ça en une seule fois !

User.joins(:subscriptions)
    .where({:validated => false} 
           | {:subscriptions => {:validated => false}}).all

Et là c’est avec un grand sourire qu’on lance ses tests vérifier qu’on a bien le résultat attendu, et c’est donc tout naturellement qu’on tire la gueule en le voyant s’allumer en rouge…

Que s’est-il passé ? C’est simple, tout se joue au début avec la jointure:

User.joins(:subscriptions)

Ceci va être traduit en SQL par un INNER JOIN. Le comportement de cette instruction est « fusionnes les éléments de cette table dans cette autre table à condition qu’il respecte telle condition », et la condition en question c’est que l’id de l’utilisateur corresponde au user_id de l’abonnement.

Que se passe-t-il si un utilisateur n’a aucun abonnement, et bien la base SQL ne trouve aucune ligne dans les abonnements à faire correspondre à l’utilisateur, et il supprime donc la ligne des résultats.

Notre joli test nous indique ainsi que tous les utilisateurs qui sont en attente de validation (et non pas la validation de leur abonnement) ne sont pas renvoyés par notre méthode.

Quelle est la solution ?

Troisième solution (la bonne)

OK sur ce coup là il va falloir me faire confiance au début.

User.joins(:subscriptions.outer)
     .where({:validated => false}
            | {:subscriptions => {:validated => false}}).all

Eh bah on était pas si loin que ça avec notre deuxième solution ! Le soucis venait simplement du joins ! Explication.

Si le INNER JOIN cherche absolument à faire correspondre la table jointe, le OUTER JOIN lui en revanche s’en fout un peu … s’il ne trouve aucune ligne dans les abonnements avec l’id de l’utilisateur courant, il le conserve quand même et considère toutes les valeurs des colonnes de la table abonnement comme vides.

Au final

Je suis passé un peu au travers de l’explication du fonctionnement précis du JOIN en lui-même, mais pour ça vous trouverez bien assez d’aide sur le inner join sur le reste du web.

Le outer join lui est souvent moins traité, voilà pourquoi j’ai voulu en faire une explication simple par un exemple pratique.  Le cas se répète, si vous faite une requête avec une jointure, mais que cette jointure est « optionnelle », alors n’oubliez pas que c’est d’un OUTER JOIN dont vous avez besoin.

2 réflexions au sujet de « Maîtriser le outer join comme un pro ! »

  1. Salut fillot,

    Quelques petites précisions sur ton article :
    1) Le left inner join ça n’existe pas …, c’est soit left join, soit inner join mais c’est pas la même chose (left join conserve les lignes de la première table qui ne matchent pas la clé, pas le inner)
    2) Oui, le full outer join (y’a un full devant en SQL Server et en Oracle il me semble bien), ça permet de conserver toutes les lignes qui ne matchent pas la clé dans les deux sens.
    3) Si c’est une vraie base de données derrière, ça ne serait pas plus propre et plus efficace d’écrire et d’appeler des procédures stockées ? parce que des gem ruby pour générer des requêtes SQL, y’a de fortes chances que ça soit loin d’être optimisé …

    Voilà, c’est tout pour le moment.

    PS1: si tu as besoin d’un expert SQL …
    PS2: faut vraiment qu’on t’invite à manger un week-end chez nous …

  2. Exact, le LEFT INNER JOIN n’existe pas en SQL. Sur la plupart des SGBD d’ailleurs, il n’est même pas nécessaire d’indiquer INNER, c’est ce qui est pris par défaut si on écrit simplement JOIN.

    LEFT, RIGHT et FULL ne concernent que le OUTER JOIN, FULL étant l’option par défaut si l’on écrit simplement OUTER JOIN.