Implémentez vos bases de données relationnelles avec SQL

our

Les bases de données SQL sont dites de type “relationnel”. Cela sous-entend que leur force réside sur leur capacité à relier plusieurs types de données entre elles.

Pour l’instant, vous avez utilisé de la donnée sans relations. Par exemple, pour récupérer ou mettre à jour un utilisateur ou un aliment.

Or, si vous reprenez l’exemple de Foodly, l’application doit stocker les aliments qu’un utilisateur a scannés. Pour ce faire, il faut stocker les relations entre ces mêmes utilisateurs et certains aliments.

Nous verrons comment mettre en place de telles relations dans la partie 4. Mais en attendant, voyons comment récupérer des objets selon les relations qu’ils ont entre eux.

Extrayez des informations via une relation 1 à plusieurs

Beaucoup d’utilisateurs vont utiliser Foodly, et ce, dans plusieurs pays.

Afin de pouvoir s’adapter à chacun, l’application va devoir stocker la langue préférée de chaque utilisateur. Pour ce faire, la table “langue” a été rajoutée à la base de données Foodly que vous avez téléchargée au début de cette partie.

Je vous laisse donc, comme vous commencez à en avoir l'habitude, sortir de MySQL, changez la version de Foodly pour la 3ème partie du cours, vous reconnecter à MySQL et sélectionner la base foodly. Faites-le, car si vous avez effectuez des modifications, nos résultats risquent de diverger...

Comme évoqué dans la vidéo, chaque utilisateur est relié à une langue. Et chaque langue peut être reliée à plusieurs utilisateurs.

Pour matérialiser une telle relation dans une base SQL telle que MySQL, on suit un principe assez simple :

1. Dans ce cas spécifique, une langue est reliée à plusieurs utilisateurs. On créé donc cet objet normalement, comme vous avez pu le faire précédemment. Cela se fait avec la commande INSERT INTO `langue` VALUES ('français'); 

2. Chaque utilisateur se voyant relié à une langue, c’est l’utilisateur qui va devoir stocker l’id unique de la langue associée. Par convention, on utilise comme nom de ce champ {nom de l’objet associé}_id  (donc ici, langue_id). Les utilisateurs de la base de données mise à jour dans la partie 2 ont ainsi un champ langue_id, où est stocké l’id de la langue qu’ils souhaitent utiliser.

Par exemple, le premier utilisateur a comme langue_id 1, soit l’id du français dans la table des langues.

Imaginez désormais qu’on vous demande de ressortir toutes les langues utilisées par les 10 premiers utilisateurs, ou tous les utilisateurs ayant configuré Foodly en anglais.

Ce serait fastidieux de tout vérifier à la main, non ? 😨

Grâce à cette commande, vous allez pouvoir expliquer à MySQL comment joindre deux tables selon un identifiant qu’elles ont en commun.

Partons du principe que :

• La langue_id du premier utilisateur est le français ;

• L’id du français est 1.

Vous allez spécifier à MySQL de joindre les tables “utilisateur” et “langue” en lui précisant que l’id de langue et langue_id de l’utilisateur doivent êtres égaux !

Prenons un exemple. Regardons tous les utilisateurs avec les langues qui leur sont associées. Tapez cette commande dans votre terminal :

SELECT * 
FROM utilisateur
JOIN langue
ON utilisateur.langue_id = langue.id;

Vous devriez obtenir ce tableau :

+----+-------------+------------+----------------------+-----------+----+-----------+
| id | nom         | prenom     | email                | langue_id | id | nom       |
+----+-------------+------------+----------------------+-----------+----+-----------+
|  1 | durantay    | quentin    | qentin@gmail.com     |         1 |  1 | français  |
|  2 | dupont      | marie      | marie@hotmail.fr     |         1 |  1 | français  |
|  5 | paul        | pierre     | pp@orange.fr         |         1 |  1 | français  |
|  6 | de vauclerc | lisa       | lisadv@gmail.com     |         1 |  1 | français  |
|  7 | gluntig     | éléonore   | glunt@sfr.com        |         1 |  1 | français  |
| 10 | tember      | fabienne   | fabienne@yopmail.com |         1 |  1 | français  |
|  3 | miller      | vincent    | vm@yahoo.com         |         2 |  2 | anglais   |
|  4 | zuckerberg  | marc       | marc@gmail.com       |         2 |  2 | anglais   |
|  8 | cavill      | henry      | henry@outlook.fr     |         2 |  2 | anglais   |
|  9 | hopper      | lionel     | hpp@gmail.com        |         2 |  2 | anglais   |
+----+-------------+------------+----------------------+-----------+----+-----------+

On y lit les langues utilisées par chaque utilisateur.

Que s’est-il passé dans cette commande ?

• Nous avons demandé à MySQL de sélectionner tous les utilisateurs grâce à  SELECT * FROM `utilisateur`. 

• Au résultat de cette commande nous avons joint la table  langue  grâce à 

  JOIN `langue`   .

• Mais pour pouvoir faire cette jointure, il faut préciser à MySQL la correspondance entre la table   langue  et la table  utilisateur  . Ici, cette correspondance est effectuée via la clé  langue_id  pour la table  utilisateur  et  id  pour la table  langue . Ce la se fait grâce à ON `utilisateur`.`langue_id` = `langue`.`id`   .

Je récapitule :

Un exemple ?

Mais avec plaisir ! Regardez ceci :

SELECT * FROM `utilisateur`
JOIN `langue`
ON `utilisateur`.`langue_id` = `langue`.`id`
WHERE (`utilisateur.email` LIKE "%gmail%") AND (`langue.id`=1) ;

C'est la liste des utilisateurs qui utilisent Gmail et qui parlent français.

On obtient :

+----+-------------+---------+------------------+-----------+----+-----------+
| id | nom         | prenom  | email            | langue_id | id | nom       |
+----+-------------+---------+------------------+-----------+----+-----------+
|  1 | durantay    | quentin | qentin@gmail.com |         1 |  1 | français  |
|  6 | de vauclerc | lisa    | lisadv@gmail.com |         1 |  1 | français  |
+----+-------------+---------+------------------+-----------+----+-----------+

On voit bien ici tout l'intérêt de la jointure. Car sans cet outil "magique", il nous aurait été impossible d'effectuer une telle requête.

Un dernier pour la route ? D'accord, mais c'est bien parce que c'est vous. 😉

Essayons ceci :

SELECT utilisateur.id, UPPER(utilisateur.nom) AS "NOM", utilisateur.prenom, utilisateur.email,langue.nom AS "LANGUE" FROM utilisateur
JOIN langue
ON utilisateur.langue_id = langue.id
WHERE (utilisateur.email LIKE "%gmail%") AND (langue.id=1) 
ORDER BY utilisateur.id DESC;

On obtient :

+----+-------------+---------+------------------+-----------+
| id | NOM         | prenom  | email            | LANGUE    |
+----+-------------+---------+------------------+-----------+
|  6 | DE VAUCLERC | lisa    | lisadv@gmail.com | français  |
|  1 | DURANTAY    | quentin | qentin@gmail.com | français  |
+----+-------------+---------+------------------+-----------+

Alors, qu'a-t-on fait ?

Et bien, on a repris la requête précédente mais on l'a (un peu) améliorée :

• Pour éviter d'avoir trop de colonnes, on a spécifié quelles colonnes on voulait.

• Pour bien distinguer les noms des prénoms, on a mis les noms en majuscules avec UPPER.

• On a trié le résultat par ordre décroissant en fonction de l'  id  .

•  On a jouté des alias pour la colonne NOM  et  LANGUE, sinon on aurait eu 

  UPPER(utilisateur.nom) et nom  à la place.

Prenez le temps de bien lire la requête, de jouer avec le code, et de bien comprendre le rôle de chaque commande ou mot clé.

Vous verrez, que petit à petit, en ajoutant les différentes notions que nous avons déjà abordées, on commence à faire des requêtes assez complexes ! 

Notez au passage qu'on aurait pu simplifier le tout en passant par ... une vue. 😇

À vous de jouer !

Admettons que je vous demande de me donner tous les noms de famille des utilisateurs ayant sélectionné le français. Comment feriez-vous cela ?

Testez la commande et vérifiez la solution ci-dessous :

Voilà, vous pouvez désormais relier entre elles deux tables unies par une relation 1 à plusieurs. Vous pouvez être fier de vous ! 👏

Maintenant, passons à la prochaine étape : les relations plusieurs à plusieurs.

Obtenez des informations complexes via une relation plusieurs à plusieurs

Reprenons ensemble l’idée première de l’application Foodly. Cette dernière sert à des utilisateurs (comme vous et moi) à scanner des aliments.

Une fois ces aliments scannés, il serait plus qu’utile que la base de données les garde en mémoire, afin que les utilisateurs puissent les retrouver par la suite (pour par exemple faire leur prochaine liste de courses).

Pour ce faire, il faudrait un moyen de stocker dans la BDD tous les aliments qui ont été scannés par un utilisateur précis. Sachant que :

• Un même utilisateur peut stocker plusieurs aliments scannés ;

• Un aliment peut lui-même être scanné par plusieurs utilisateurs.

On parle ici de relation plusieurs à plusieurs ou many-to-many en anglais . Chaque objet d’une table pouvant être relié à plusieurs objets de l’autre table, et vice versa.

Or, tout ce que sait faire MySQL (et les bases de données SQL en général), c’est de stocker une valeur unique par champ. Il n’est pas possible par exemple de stocker plusieurs id d’aliments au sein d’un même utilisateur.

Vous allez “tricher”. Une relation plusieurs à plusieurs, c’est une multitude de relations 1 à plusieurs.

Regardez les tables présentes dans la BDD que vous avez téléchargées pour cette partie. Voyez-vous une table appelée  utilisateur_aliment ?

Celle-ci contient des  utilisateur_id   et des  aliment_id  . Vous l’avez peut-être deviné : elle sert à stocker des relations entre un utilisateur et un aliment précis.

Par convention, les tables de liaisons prennent le nom  {table1}_{table2}  , et servent à relier les tables 1 et 2 qui y sont stockées, en sauvegardant l’id d’un objet de la table 1, à l’id de l’objet de la table 2 correspondant.

Je rappelle au passage que les conventions sont faites pour être respectées. Certes, vous n'êtes pas obligé de le faire, et vous pouvez appeler votre table de liaison  blabla  si cela vous chante...

En récupérant tous les objets présents dans cette base, qui ne sont autres que des relations 1 à plusieurs vers utilisateur et aliment, on peut reconstituer les relations plusieurs à plusieurs entre ces mêmes utilisateurs et aliments !

Eh bien, il s’agit toujours d’un bon JOIN  !

Voici la commande pour relier tous les utilisateurs aux aliments qu’ils ont scannés :

SELECT *
FROM utilisateur
JOIN utilisateur_aliment ON (utilisateur.id = utilisateur_aliment.utilisateur_id)
JOIN aliment ON (aliment.id = utilisateur_aliment.aliment_id);

Décomposons cette commande ensemble :

• Nous avons demandé à MySQL de sélectionner tous les utilisateurs avec SELECT * FROM utilisateur

• Auxquels nous voulons joindre la table utilisateur_aliment  avec  JOIN utilisateur_aliment

• En précisant à MySQL de les relier en considérant que l’id de l’utilisateur est stocké en tant que  utilisateur_id  dans la table utilisateur_aliment  ON (utilisateur.id = utilisateur_aliment.utilisateur_id)

• À ce JOIN  , on veut à nouveau lier de la donnée de la table aliment, soit un nouveau  JOINavec  JOIN aliment

• Pour ce faire, on précise à MySQL que l’id de l’aliment est stocké dans utilisateur_aliment en tant que aliment_id avec  ON (aliment.id = utilisateur_aliment.aliment_id)

On obtient ce magnifique tableau où sont liés tous les utilisateurs aux aliments qu’ils ont scannés.

À vous de jouer !

Admettons que vous souhaitiez voir tous les aliments sélectionnés par les utilisateurs dont l’adresse e-mail et une adresse Gmail. Comment feriez-vous ?

Faites le test et vérifiez votre commande avec ce screencast :

Vous voilà capable de relier entre elles deux tables ayant une relation plusieurs à plusieurs, via leur table de liaison. Vous êtes très fort. 💪

En résumé

• Pour effectuer une jointure entre deux tables, on utilise les mots clés  JOIN  et  ON  .

• On peut utiliser l'opérateur *  ou on peut spécifier les colonnes que nous souhaitons sélectionner.

• La requête fonctionne comme une requête normale. On peut rajouter des conditions avec WHERE , trier avec  ORDER BY  ou créer des alias avec  AS  .

• Pour une relation one-to-many, la jointure se fait naturellement en spécifiant les deux colonnes à faire correspondre.

• Pour les relations many-to-many, il faut passer par une table d'association qui lie les deux tables entre elles.

• Pour faire une requête many-to-many, on fait donc une double jointure de notre table initiale à notre table de liaison, puis de notre table de liaison à notre table cible.

   

Maintenant que vous savez donner du sens à la donnée présente dans votre BDD, il vous reste une ultime partie !

Vous allez y apprendre comment modifier la structure d’une base de données, afin de la faire évoluer selon les besoins de votre application.