keyboard_arrow_up

SQL, jointures et critères multiples

Rédigé par Sylvain Lavielle
Développeur web freelance expert Drupal sur Toulouse

Le 26/06/2019

 

Récemment, j'ai travaillé sur un projet nécessitant de rechercher dans une base SQL des profils particuliers en fonction de critères assez complexes et potentiellement assez nombreux. Après un peu de recherche j'ai pu développer mon module de requêtage sur un modèle de requête répondant à mon besoin.

Pour cet article je vais me contenter de présenter l'exemple en bon vieux SQL de base en oubliant pour un moment les ORM (Symfony, Laravel) et les databases API (Drupal).

Point de départ

Voici un cas très simplifié mais explicite du modèle en question.

Nous avons 3 tables :

Une table people contenant des personnes

CREATE TABLE `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Une table color contenant des couleurs,

CREATE TABLE `color` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Une table people_liked_color permettant de joindre les personnes et les couleurs pour définir quelles couleurs ces personnes aiment.

CREATE TABLE `people_liked_color` (
  `id_people` int(11) DEFAULT NOT NULL,
  `id_color` int(11) DEFAULT NOT NULL,
  KEY `index1` (`id_people`),
  KEY `index2` (`id_color`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

La table people contient 3 personnes :

id name
1 Jean
2 Paul
3 Stéfanie

La table color contient 3 couleurs :

id name
1 Vert
2 Bleu
3 Rouge

La table people_liked_color contient les associations suivantes :

id_people id_color
1 1
1 2
2 1
2 2
2 3
3 2
3 3

Le besoin

Le besoin est assez simple : écrire une requête permettant de lister toutes les personnes qui à la fois aiment le rouge et le bleu

Cela semble plutôt basique hein ? essayons pour voir ...

Un premier essai

Pour simplifier encore un peu l'exemple, nous allons laisser de côté la table color dont nous n'avons finalement besoin que pour avoir les libellés des couleurs.

De prime abord, on se dit qu'il va falloir joindre les tables people <- people_liked_color  puis d'écrire des conditions regroupées dans une clause WHERE pour nos critères de couleur.

SELECT p.id, p.name, plc.id_color
FROM people AS p 
LEFT JOIN people_liked_color plc ON plc.id_people = p.id
WHERE c.id = 2 AND c.id = 3

Mais bien sûr cela ne peut pas fonctionner ainsi car la colonne id de la table color ne peut avoir qu'une valeur pour chaque ligne de résultat et cette valeur ne peut donc pas être égale à 2 et 3 en même temps.

Un peu plus d'inspiration

En suivant ce raisonnement on comprend vite qu'il va falloir non pas une jointure mais deux (une pour chaque condition) sur la table people_liked_color pour pouvoir comparer plusieurs valeurs simultanément.

Pour chaque jointure on ajoute à la close ON un test permettant de ne considérer que les valeurs qui nous intéressent pour chacune des conditions.

SELECT p.id, p.name, plc.id_color, plc2.id_color FROM people AS p 
LEFT JOIN people_liked_color plc ON plc.id_people = p.id AND plc.id_color = 2 
LEFT JOIN people_liked_color plc2 ON plc2.id_people = p.id AND plc2.id_color = 3 
id name id_color id_color
1 Jean 2 NULL
2 Paul 2 3
3 Stéfanie 2 3

Puis dans la clause WHERE, on ajoute nos 2 conditions de filtrage

WHERE plc.id_color = 2 AND plc2.id_color = 3 

Ainsi cela retourne le résultat attendu.

id name id_color id_color
2 Paul 2 3
3 Stéfanie 2 3

Évidemment, cela multiplie les jointures, mais c'est la seule possibilité pour effectuer ce type de recherche sur des valeurs multiples pour une colonne donnée.

Un os : le dépassement de la capacité de jointure MySQL

Dans l'exemple ci-dessus nous avons présenté un exemple simple n'occasionnant que 2 jointures. Cependant, si nous avons de multiples tables avec de nombreuses valeurs à tester, le nombre de jointures peut devenir très important. et cela peut causer plusieurs soucis :

  • MySQL Peut se montrer très lent dès que le nombre de jointures devient important (en particulier si le paramètre optimizer_search_depth est paramétré avec sa valeur par défaut),
  • Il dispose d'une limite incontournable du nombre de jointures possibles dans une requête et cette limite est de 61 jointures.

Dans le projet sur lequel j'ai travaillé cette limite de 61 tables pouvait être atteinte. Cela correspond par exemple à effectuer une recherche sur 9 tables avec 7 conditions simultanées sur chaque (soit 63 jointures). Nous avons donc dû mettre en place une stratégie pour contourner cette limitation.

La solution des requêtes en cascades

La solution réside dans l'utilisation de sous-requêtes en cascade dans la clause WHERE, chaque requête imbriquée limitant la recherche de la requête supérieure à son résultat.

Pour illustrer cette explication d'un exemple, ajoutons 2 tables similaires à ce que nous avons pour les couleurs, mais pour des animaux.

Table animal :

id name
1 chat
2 chien
3 crocodile

 Table people_liked_animal qui permet de définir quels animaux les différentes personnes aiment :

id_people id_animal
1 1
1 2
2 1
2 3
3 2
3 3

Voici à quoi ressemble notre requête en cascade :

SELECT p.id, p.name FROM people AS p 
LEFT JOIN people_liked_color plc ON plc.id_people = p.id AND plc.id_color = 2 
LEFT JOIN people_liked_color plc2 ON plc2.id_people = p.id AND plc2.id_color = 3
WHERE plc.id_color = 2 # aime le bleu
AND plc2.id_color = 3 # aime le rouge
AND p.id IN (
    SELECT p.id FROM people AS p 
    LEFT JOIN people_liked_animal pla ON pla.id_people = p.id AND pla.id_animal = 2 
    LEFT JOIN people_liked_animal pla2 ON pla2.id_people = p.id AND pla2.id_animal = 3	
    WHERE pla.id_animal = 2 # aime les chiens
    AND pla2.id_animal = 3 # aime les crocodiles
    AND p.id IN (
        # Et on peut continuer ainsi encore et encore
    )
)

La requête nous donne le résultat suivant :

id name
3 Stéfanie

Ainsi j'ai deux raisons de me réjouir :

  • La requête donne bien le résultat attendu
  • Et si un jour je trouve un chien bleu et un crocodile rouge je sais que je pourrais les donner à Stéfanie qui aime à la fois le bleu, le rouge, les chiens et (ce qui est plus rare), les crocodiles :)

Un dernier mot ?

Chaque niveau de requête peut contenir 61 jointures, ce qui rend la limite beaucoup plus supportable puisque pour atteindre ce nombre dans le modèle proposé il faudrait tester 61 conditions simultanément sur la même table (puisque dans notre modèle on a l'ensemble des conditions relatives à une table regroupé sur une requête).

Sujets abordés dans cet article