Ajout d'une colonne dans le resultat

**Sylvercis** · 28/01/2020, 23h08

Bonjour,

J'ai une base de données sur les films et je souhaite sortir dans le même resultat:
- La liste de toutes les personnalites répertoriées
- Le/les réalisateurs pour un film en particulier

Le resultat que j'attends est celui ci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
id   prenom  	nom	           selected 	
2 		Peter Howitt 	    true
3 		Antoine Fuqua 	    true
1 		Fabienne Berthaud    NULL
4 		McG      	       NULL
5 		Walt Becker 	    NULL
6 		John Singleton 	    NULL
7 		John Stockwell 	    NULL
8 		Tim Johnson            NULL
9 		Patrick Gilmore  NULL
10 		Rob Schmidt         NULL

Alors oui, le prenom et le nom sont encore dans la même colonne pour le moment ("nom"), ça n'a pas d'importance ici.

Avec ce resultat j'ai donc une liste complète de toutes les personnalites avec en plus dans la colonne "selected" un drapeau pour reperer la ou les personnalites qui ont le role de realisateur dans le film indiqué en paramètre dans la requete.

Les tables se presentent comme ça:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
 
--
-- Base de données :  `essai`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `films`
--
 
DROP TABLE IF EXISTS `films`;
CREATE TABLE IF NOT EXISTS `films` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Numéro id du film',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5348 DEFAULT CHARSET=utf8;
 
--
-- Déchargement des données de la table `films`
--
 
INSERT INTO `films` (`id`) VALUES
(2),
(3),
(7),
(11),
(15),
(27);
 
-- --------------------------------------------------------
 
--
-- Structure de la table `films_realisateurs`
--
 
DROP TABLE IF EXISTS `films_realisateurs`;
CREATE TABLE IF NOT EXISTS `films_realisateurs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `film_id` int(10) UNSIGNED NOT NULL COMMENT 'id du film (TABLE films)',
  `personne_id` int(10) UNSIGNED NOT NULL COMMENT 'id du réalisateur (TABLE personnes)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5688 DEFAULT CHARSET=utf8 COMMENT='Cette table contient le ou les réalisateurs pour chaques fil';
 
--
-- Déchargement des données de la table `films_realisateurs`
--
 
INSERT INTO `films_realisateurs` (`id`, `film_id`, `personne_id`) VALUES
(1, 3, 5),
(2, 7, 2),
(3, 7, 3),
(4, 11, 7),
(5, 15, 5);
 
-- --------------------------------------------------------
 
--
-- Structure de la table `personnalites`
--
 
DROP TABLE IF EXISTS `personnalites`;
CREATE TABLE IF NOT EXISTS `personnalites` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `prenom` varchar(50) DEFAULT '' COMMENT 'prénom de la personne',
  `nom` varchar(50) DEFAULT NULL COMMENT 'nom de la personne',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3551 DEFAULT CHARSET=utf8 COMMENT='Cette table contient les infos de toutes les personalités';
 
--
-- Déchargement des données de la table `personnalites`
--
 
INSERT INTO `personnalites` (`id`, `prenom`, `nom`) VALUES
(1, '', 'Fabienne Berthaud'),
(2, '', 'Peter Howitt'),
(3, '', 'Antoine Fuqua'),
(4, '', 'McG '),
(5, '', 'Walt Becker'),
(6, '', 'John Singleton'),
(7, '', 'John Stockwell'),
(8, '', 'Tim Johnson'),
(9, '', ' Patrick Gilmore'),
(10, '', 'Rob Schmidt');
COMMIT;

j'ai écrit cette requête qui fonctionne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
		SELECT p.id id, p.prenom, p.nom, r.selected
		FROM personnalites p
		LEFT JOIN (
    		SELECT personne_id id, 'true' selected
    		FROM films_realisateurs r
    		WHERE r.film_id = 7
		) r ON r.id = p.id

Avec les données ci dessus, pas de problème c'est rapide: 0s

Par contre avec 5000 personnalites et 5000 films, la requete mets 1.5s, ce qui me parait beaucoup trop.
Les 2 select séparement sortent en 0s donc c'est la jointure qui prends du temps.

Pourriez vous me dire comment tourner la requête pour obtenir un résultat plus rapidement.
Le contenu de la colonne "selected" n'a pas d'importance, ça peut être true/false, 0/1, x/NULL.
Cette colonne est ensuite lue par une fonction php pour pré-selectionner ces personnes dans une liste.

**CinePhil** · 28/01/2020, 23h54

Bonjour,

Pas mal de choses à dire...

1) Nommez vos tables au singulier.
Chaque ligne de la table représente 1 film, 1 personnalité...

2) Votre table films_realisateurs est une table associative.
En ce cas, pas besoin d'identifiant auto-incrémenté. La clé primaire de la table est constituée par les clés étrangères référençant les deux tables entrant dans l'association.

3) Une clé primaire est, par définition, déjà munie d'une contrainte d'unicité.
Inutile donc de déclarer en plus une unicité sur la même colonne clé primaire.

4) Bizarre d'avoir une table des films avec seulement un identifiant !

5) Quel est l'intérêt de sortir la liste de toutes les personnalités pour n'indiquer true que pour celles qui sont associées au film choisi ?
Vous cherchez les personnalités associées au film n° 7 ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT p.nom, p.prenom
FROM films_realisateurs fr
INNER JOIN personnalités p ON p.id = fr.personne_id
WHERE fr.film_id = 7

6) Si ceci reste lent, pensez tout simplement à bien indexer vos tables.
Il faut notamment indexer la seconde clé étrangère entrant dans la composition de la clé primaire de la table associative (quand vous aurez supprimé votre id auto-incrémenté inutile).

**Sylvercis** · 29/01/2020, 07h02

2) Votre table films_realisateurs est une table associative.
En ce cas, pas besoin d'identifiant auto-incrémenté.

Oui, je me suis rendu compte de mon erreur quand j'ai décidé de poster ce message.
Je mets toujours un id sur chaque table pour le cas où je veux cibler une ligne pour l'effacer mais c'est vrai que pour ce genre de table de toute façon j'utiliserai toujours les deux autres clés pour identifier la ligne.
Je l'ai laissé ici pour avoir la confirmation de mon erreur.

4) Bizarre d'avoir une table des films avec seulement un identifiant !

J'ai d'autres colonnes, ici je l'ai simplifié.
J'ai posté cette table uniquement pour le cas où l'on me dirai, "bizarre de ne pas avoir de table Film".

5) Quel est l'intérêt de sortir la liste de toutes les personnalités pour n'indiquer true que pour celles qui sont associées au film choisi ?
Vous cherchez les personnalités associées au film n° 7 ?

J'ai en html/javascript un select qui contient toutes les personnalites + la selection du/des realisateurs.
Il me faut donc de préférence un resultat conforme a ce que j'ai donné dans mon message.

Si ce n'est pas possible, je ferai alors 2 requetes mais ça m'obligera a faire le tri ensuite en PHP au lieu de le faire en SQL

**CinePhil** · 29/01/2020, 12h59

J'ai en html/javascript un select qui contient toutes les personnalites + la selection du/des realisateurs.

Vous voulez dire que quand vous aurez 5000 personnalités, vous allez importer dans votre page les 5000 dans une liste déroulante ?

Faites plutôt de l'auto-complétion sur le nom du réalisateur saisi !

**Sylvercis** · 29/01/2020, 17h23

oui, j'utilise ça:

https://select2.org/getting-started/basic-usage

**CinePhil** · 29/01/2020, 17h54

On sort de l'objet de ce forum mais importer un gros paquet de données dans un page HTML, ça prend du temps et des ressources.

A titre d'exemple, j'avais écrit en 2011 un billet de blog sur la sélection d'une ville par interrogation en temps réel de la BDD sans devoir importer les 36500 communes françaises.

Et plus récemment, j'ai publié un billet sur les listes de sélection enchaînées qui, elles aussi, interrogent la BDD en temps réel.

Ajout d'une colonne dans le resultat

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager