MySQL 5.7.11 / PhpMyAdmin : 4.9.3

Bonjour,

Je rencontre un problème avec une requête que je transformerai plus tard en procédure.
L'objectif est de retrouver des informations à partir d'un critère dans une colonne. Voici la requête que j'ai essayé :

SET @motcle = '%affaire%';

SELECT DC_Episodes.Id AS Id,
		DC_Episodes.Numero AS Numero,
		CONCAT(
	(SELECT CONCAT('Du volume ', DC_Volumes.Volumes, ', dossier ', DC_Volumes.Dossiers)
	FROM DC_Volumes
	WHERE DC_Volumes.Id = (
SELECT 
	CASE WHEN DC_Episodes.Origine <> 'Histoire Originale' THEN MIN(DC_Volumes.Id)
	ELSE 'Histoire Originale' END
	FROM DC_Volumes
INNER JOIN DC_ResVol_Dossiers
		ON (DC_Volumes.Id = DC_ResVol_Dossiers.Did)
INNER JOIN DC_ResVol_Resume
		ON (DC_ResVol_Dossiers.Rid = DC_ResVol_Resume.Id)		
INNER JOIN DC_ResVol_episodes
		ON (DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid)
INNER JOIN DC_Episodes
		ON (DC_ResVol_Episodes.Episode_Id = DC_Episodes.Id)
WHERE DC_Episodes.Titre_fr LIKE @motcle)),	
(SELECT CONCAT(' au volume ', DC_Volumes.Volumes, ', dossier ', DC_Volumes.Dossiers)
	FROM DC_Volumes
	WHERE DC_Volumes.Id = (
SELECT 
	CASE WHEN DC_Episodes.Origine <> 'Histoire Originale' THEN MAX(DC_Volumes.Id)
	ELSE 'Histoire Originale' END
	FROM DC_Volumes
INNER JOIN DC_ResVol_Dossiers
		ON (DC_Volumes.Id = DC_ResVol_Dossiers.Did)
INNER JOIN DC_ResVol_Resume
		ON (DC_ResVol_Dossiers.Rid = DC_ResVol_Resume.Id)		
INNER JOIN DC_ResVol_episodes
		ON (DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid)
INNER JOIN DC_Episodes
		ON (DC_ResVol_Episodes.Episode_Id = DC_Episodes.Id)
WHERE DC_Episodes.Titre_fr LIKE @motcle))) AS Volume,	
		DC_Episodes.Titre_Jap AS Titre_original,
		DC_Episodes.Titre_ang AS Titre_anglais,
		DC_Episodes.Titre_fr AS Titre_français,
		DC_Episodes.Titre_it AS Titre_italien,
		DC_Episodes.Titre_es AS Titre_espagnol,
		DC_Episodes.Titre_de AS Titre_allemand,
		DC_Episodes.Type AS Type_Episode,
		DC_Episodes.Duree AS Duree_Episode,
		(SELECT GROUP_CONCAT(DISTINCT DC_ResVol_Resume.Id SEPARATOR ', ')
			FROM DC_ResVol_Resume
			INNER JOIN DC_ResVol_Episodes
				ON DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid
		WHERE DC_ResVol_Episodes.Episode_Id = DC_Episodes.Id) AS Voir_Histoire
FROM (DC_Episodes,DC_ResVol_Resume)
WHERE DC_Episodes.Titre_fr LIKE @motcle
GROUP BY DC_Episodes.Id;

La requête plante et reste figée sur "Chargement en cours". Lorsque j'affiche la variable en faisant

SELECT @motcle;

Il me retourne la bonne valeur.

Si je mets les valeurs brutes :

DC_Episodes.Titre_fr LIKE '%affaire%'

La requête aboutit normalement et retourne 9 résultats.

Quand je remplace les lignes (dans les trois occurences) :

SET @motcle = '%affaire%';
DC_Episodes.Titre_fr LIKE @motcle

par :

SET @motcle = 'affaire';
DC_Episodes.Titre_fr LIKE CONCAT('%',@motcle,'%')

Là, PhpMyAdmin reste bloqué sur "Chargement en cours".

Pour remettre dans le contexte : en amont, il y a un formulaire de recherche : dans une case INPUT, les utilisateurs mettent leurs mots-clés. Un code PHP vérifie les mots-clés et remplace les éventuels astérisques par un pourcentage.

J'ai du mal à percevoir pourquoi la variable ne passe pas.

2e problème, dans le cours "Administrez vos bases de données avec MySQL", il est clairement dit :
"Cependant, il n'est pas possible d'utiliser les variables utilisateur pour stocker un nom de table ou de colonne que l'on introduirait ensuite directement dans la requête."

Dans mon cas, j'ai plusieurs colonnes, dans la même table : Titre_Fr, Titre_de, Titre_it, Titre_es, etc. Quelle méthode puis-je utiliser pour pouvoir faire les recherches dans une colonne spécifiée (celle mentionnée par l'utilisateur), sans utiliser de variable ?

En vous remerciant par avance pour votre aide.

EDIT : Correction des symptômes.

-
Edité par KerberosK 2 janvier 2020 à 22:26:01

Bonjour,

KerberosK a écrit:

La requête aboutit normalement et retourne 9 résultats

Je ne vois pas comment ce résultat peut être correct ...

Si ta requête tourne en boucle, c'est sûrement à cause des SELECT imbriqués (à éviter autant que possible, les jointures sont là pour ça) et du produit cartésien généré par l'absence de jointure dans ta requête principale (pas de jointure entre épisodes et résumé). Sans parler de ce GROUP BY qui ne sert à rien à la dernière ligne ...

Dans tes sous-requêtes, ton CASE peut retourner un texte ("Histoire Originale") ou un id (DC_Volumes.Id) ... et la sous-requête peut retourner plusieurs valeurs également ... deux points problématiques dans une égalité ...

Manque de maîtrise du langage SQL ...

Sans connaître vraiment le fonctionnement de ta base et les cardinalités entre les tables, il est difficile de t'aider plus, peux-tu détailler un peu les tables, ce qu'elles stockent et les cardinalités ? Globalement, il semble un peu mal ficelé ...

KerberosK a écrit:

Quelle méthode puis-je utiliser pour pouvoir faire les recherches dans une colonne spécifiée (celle mentionnée par l'utilisateur), sans utiliser de variable ?

Il te faut utiliser des requêtes préparées. Et le paragraphe du cours : https://openclassrooms.com/fr/courses/1959476-administrez-vos-bases-de-donnees-avec-mysql/1971264-requetes-preparees

-
Edité par Benzouye 3 janvier 2020 à 13:46:29

Bonjour,

Merci pour ta réponse.

Voici comment est construite ma base de données. J'ai encadré les colonnes qui doivent être retournées quand on fait la requête que j'ai indiquée précédemment.



Pour remettre dans le contexte : Il s'agit d'un manga constitué de plusieurs chapitres. Un chapitre peut appartenir à une ou plusieurs histoires.
Pour suivre mon schéma : DC_Volumes liste tous les chapitres et DC_ResVol_Resume liste les histoires ; DC_ResVol_dossiers fait le lien entre les chapitres et les histoires.

Le manga a été tourné en animé (DC_Episodes). Mais dans l'animé, il y a des épisodes qui n'ont rien à voir avec le manga (Colonne "Origine" "Histoire originale"). D'autres épisodes sont l'adaptation d'un ou plusieurs chapitres.

Je viens de me rendre compte que le résultat de la requête avec les valeurs brutes ne convient pas : il remplit systématiquement la colonne "Manga", même quand il n'y a rien à mettre. Pourtant je suis parti de la requête suivante qui fonctionne :

SET @numepisode = p_episode;
SELECT DC_Episodes.Id AS Id,
        DC_Episodes.Numero AS Numero,
        CONCAT(
    (SELECT CONCAT('Du volume ', DC_Volumes.Volumes, ', dossier ', DC_Volumes.Dossiers)
    FROM DC_Volumes
    WHERE DC_Volumes.Id = (
SELECT
    CASE WHEN DC_Episodes.Origine <> 'Histoire Originale' THEN MIN(DC_Volumes.Id)
    ELSE 'Histoire Originale' END
    FROM DC_Volumes
INNER JOIN DC_ResVol_Dossiers
        ON (DC_Volumes.Id = DC_ResVol_Dossiers.Did)
INNER JOIN DC_ResVol_Resume
        ON (DC_ResVol_Dossiers.Rid = DC_ResVol_Resume.Id)       
INNER JOIN DC_ResVol_episodes
        ON (DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid)
INNER JOIN DC_Episodes
        ON (DC_ResVol_Episodes.Episode_Id = DC_Episodes.Id)
WHERE DC_Episodes.Id = @numepisode)),   
(SELECT CONCAT(' au volume ', DC_Volumes.Volumes, ', dossier ', DC_Volumes.Dossiers)
    FROM DC_Volumes
    WHERE DC_Volumes.Id = (
SELECT
    CASE WHEN DC_Episodes.Origine <> 'Histoire Originale' THEN MAX(DC_Volumes.Id)
    ELSE 'Histoire Originale' END
    FROM DC_Volumes
INNER JOIN DC_ResVol_Dossiers
        ON (DC_Volumes.Id = DC_ResVol_Dossiers.Did)
INNER JOIN DC_ResVol_Resume
        ON (DC_ResVol_Dossiers.Rid = DC_ResVol_Resume.Id)       
INNER JOIN DC_ResVol_episodes
        ON (DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid)
INNER JOIN DC_Episodes
        ON (DC_ResVol_Episodes.Episode_Id = DC_Episodes.Id)
WHERE DC_Episodes.Id = @numepisode))) AS Volume,   
        DC_Episodes.Titre_Jap AS Titre_original,
        DC_Episodes.Titre_ang AS Titre_anglais,
        DC_Episodes.Titre_fr AS Titre_français,
        DC_Episodes.Titre_it AS Titre_italien,
        DC_Episodes.Titre_es AS Titre_espagnol,
        DC_Episodes.Titre_de AS Titre_allemand,
        DC_Episodes.Type AS Type_Episode,
        DC_Episodes.Duree AS Duree_Episode,
        (SELECT GROUP_CONCAT(DISTINCT DC_ResVol_Resume.Id SEPARATOR ', ')
            FROM DC_ResVol_Resume
            INNER JOIN DC_ResVol_Episodes
                ON DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid
        WHERE DC_ResVol_Episodes.Episode_Id = DC_Episodes.Id) AS Voir_Histoire
FROM (DC_Episodes,DC_ResVol_Resume)
WHERE DC_Episodes.Numero = @numepisode
GROUP BY DC_Episodes.Id;

Cette requête fonctionne et se fait sur le numéro d'épisodes. Voici deux exemples de résultat :

J'ai juste remplacé :

SET @numepisode = p_episode;
DC_Episodes.Numero = @numepisode

par

SET @motcle = '%affaire%';
DC_Episodes.Titre_fr LIKE @motcle

La seule différence, c'est que dans la recherche par mot-clé, il peut y avoir plusieurs résultats. Et ça coince sur le chargement, quand la recherche directe fonctionne (avec une erreur dans la colonne Manga)

J'ai bien conscience que la présence des sous-requêtes ralentit l'exécution. Mais, même en faisant des jointures, j'ai dû mal à voir comment ne pas faire des sous-requêtes.
Le GROUP BY DC_Episodes.Id est important. Sans lui, j'obtiens 2241 résultats au lieu de 9, avec des doublons.

Si je procède par étapes, quand je fais la requête sur DC_Episodes, il y a une partie simple où je récupère les éléments de la même table :

SET @motcle = '%affaire%';

SELECT DC_Episodes.Id,
		DC_Episodes.Numero AS Numero,
		DC_Episodes.Titre_Jap AS Titre_original,
		DC_Episodes.Titre_ang AS Titre_anglais,
		DC_Episodes.Titre_fr AS Titre_français,
		DC_Episodes.Titre_it AS Titre_italien,
		DC_Episodes.Titre_es AS Titre_espagnol,
		DC_Episodes.Titre_de AS Titre_allemand,
		DC_Episodes.Type AS Type_Episode,
		DC_Episodes.Duree AS Duree_Episode	
	FROM DC_Episodes
	WHERE DC_Episodes.Titre_fr LIKE @motcle
	ORDER BY DC_Episodes.Id ASC;

Là, aucun problème. Là où cela se corse, c'est dans l'hypothèse où l'épisode serait une adaptation du manga et non une "histoire originale" (d'où le CASE WHEN) : dans ce cas je veux récupérer les informations sur l'histoire (DC_ResVol_Resume.Id) . Par extension, je souhaite récupérer les informations sur les chapitres : Le premier (MIN) et le dernier (MAX).

Comme tu dis, "Manque de maîtrise du langage SQL", à mon grand dam, je n'ai que quelques bases en SQL et je m'efforce d'apprendre davantage. Si je travaille sur ce manga, c'est parce que, à mon avis, en plus d'être un bon manga, c'est un bon cas pratique pour travailler les bases de données et parce que cela me donne envie d'apprendre.

Merci.

EDIT : Modification de l'image des tables.

-
Edité par KerberosK 3 janvier 2020 à 16:05:34

KerberosK a écrit:

dans la recherche par mot-clé, il peut y avoir plusieurs résultats

Oui c'est un point que j'avais souligné ...

Benzouye a écrit:

la sous-requête peut retourner plusieurs valeurs également

Mais dans tous les cas les SELECT imbriqués ce n'est pas bon ... la requête est réévaluées pour chaque enregistrement, dès que le volume à traiter est important les temps de réponse explosent ... et même si la requête avec un id fonctionne je te conseille de ne pas fonctionner ainsi ...

Je te conseille de commencer par passer les SELECT imbriqués dans des sous-requêtes de la clause FROM, voire même la création de vue (VIEW) pour simplifier la requête principale.

Pour cela, créons une sous-requête (je préfère une vue) avec l'id minimum et maximum de chapitre par épisode :

CREATE VIEW chapitre_min_max AS
SELECT 
	HE.Episode_Id,
	MIN( C.id ) AS chap_min,
	MAX( C.id ) AS chap_max
FROM
	DC_Volumes C
		INNER JOIN DC_ResVol_Dossiers CH
			ON DC_Volumes.Id = DC_ResVol_Dossiers.Did
		INNER JOIN DC_ResVol_Resume H
			ON DC_ResVol_Dossiers.Rid = DC_ResVol_Resume.Id
		INNER JOIN DC_ResVol_episodes HE
			ON DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid
GROUP BY HE.Episode_Id;

Maintenant que cette vue est créée, tu peux l'utiliser comme bon te semble dans ta requête principale.

A partir de cette requête tu peux mettre la clause WHERE que tu veux ... Soit avec l'id de l'épisode directement, soit avec un LIKE ...

SELECT
	E.Id AS Id,
	E.Numero AS Numero,
	IF(
		E.Origine = 'Histoire Originale',
		'Histoire Originale',
		CONCAT(
			CONCAT( 'Du volume ', V1.Volumes, ', dossier ', V1.Dossiers ),
			' ',
			CONCAT( 'au volume ', V2.Volumes, ', dossier ', V2.Dossiers )
		)
	) AS Volume,
	E.Titre_Jap AS Titre_original,
	E.Titre_ang AS Titre_anglais,
	E.Titre_fr AS Titre_français,
	E.Titre_it AS Titre_italien,
	E.Titre_es AS Titre_espagnol,
	E.Titre_de AS Titre_allemand,
	E.Type AS Type_Episode,
	E.Duree AS Duree_Episode,
	GROUP_CONCAT( DISTINCT RE.Rid SEPARATOR ', ' ) AS Voir_Histoire
FROM
	DC_Episodes E
		LEFT JOIN chapitre_min_max C
			ON E.id = C.Episode_Id
		LEFT JOIN DC_Volumes V1
			ON C.chap_min = V1.id
		LEFT JOIN DC_Volumes V2
			ON C.chap_max = V2.id
		LEFT JOIN DC_ResVol_episodes RE
			ON RE.Episode_Id = E.Id
WHERE
	E.Titre_fr LIKE @motcle
	OR E.Numero = @numepisode
GROUP BY E.id;

C'est bien plus facile à gérer et à maintenir non ?

-
Edité par Benzouye 3 janvier 2020 à 16:45:48

Bonjour,

Tout d'abord, un immense merci. Cela fonctionne très bien. Je n'étais pas encore arrivé aux chapitres sur les vues. Tu m'as permis d'en découvrir toute l'utilité. Juste sur la vue :

CREATE VIEW chapitre_min_max AS
SELECT
    HE.Episode_Id,
    MIN( C.id ) AS chap_min,
    MAX( C.id ) AS chap_max
FROM
    DC_Volumes C
        INNER JOIN DC_ResVol_Dossiers CH
            ON DC_Volumes.Id = DC_ResVol_Dossiers.Did
        INNER JOIN DC_ResVol_Resume H
            ON DC_ResVol_Dossiers.Rid = DC_ResVol_Resume.Id
        INNER JOIN DC_ResVol_episodes HE
            ON DC_ResVol_Resume.Id = DC_ResVol_Episodes.Rid
GROUP BY HE.Episode_Id;

MySQL retourne une erreur sur le nom des champs dans les jointures. J'ai remplacé le nom de chaque table par leur alias et cela fonctionne.

Pour être sûr d'avoir bien saisi les différences :

Une vue = Requête enregistrée pouvant être appelée comme une table dans une autre requête.
Une procédure = Requête enregistrée appelée par CALL() avec ses éventuels paramètres. Indépendante.
Une requête préparée = Requête existante uniquement pour la session en cours qui peut être préparée à partir d'une chaîne de caractères.

Correct ?

J'ai élaboré ma requête préparée à partir de ton code puis je l'ai incluse dans une procédure :

DELIMITER |
CREATE PROCEDURE ProcVoirInfoEpisodeParTitre(IN p_langue VARCHAR(300), IN p_motcle VARCHAR(300))
BEGIN

SET @langue = p_langue;
SET @motcle = p_motcle;

SET @recherche_p1 = "SELECT
    E.Id AS Id,
    E.Numero AS Numero,
    IF(
        E.Origine = 'Histoire Originale',
        'Histoire Originale',
        CONCAT(
            CONCAT( 'Du volume ', V1.Volumes, ', dossier ', V1.Dossiers ),
            ' ',
            CONCAT( 'au volume ', V2.Volumes, ', dossier ', V2.Dossiers )
        )
    ) AS Volume,
    E.Titre_Jap AS Titre_original,
    E.Titre_ang AS Titre_anglais,
    E.Titre_fr AS Titre_français,
    E.Titre_it AS Titre_italien,
    E.Titre_es AS Titre_espagnol,
    E.Titre_de AS Titre_allemand,
    E.Type AS Type_Episode,
    E.Duree AS Duree_Episode,
    GROUP_CONCAT( DISTINCT RE.Rid SEPARATOR ', ' ) AS Voir_Histoire
FROM
    DC_Episodes E
        LEFT JOIN chapitre_min_max C
            ON E.id = C.Episode_Id
        LEFT JOIN DC_Volumes V1
            ON C.chap_min = V1.id
        LEFT JOIN DC_Volumes V2
            ON C.chap_max = V2.id
        LEFT JOIN DC_ResVol_episodes RE
            ON RE.Episode_Id = E.Id
WHERE
    E.";
	
SET @recherche_p2 = " LIKE '";	
SET @recherche_p3 = "' GROUP BY E.id";
SET @recherche_complete = CONCAT(@recherche_p1,@langue,@recherche_p2,@motcle,@recherche_p3);

PREPARE ReqInfoEpisode
FROM @recherche_complete;
EXECUTE ReqInfoEpisode;
DEALLOCATE PREPARE ReqInfoEpisode;

END |

La procédure fonctionne. Cependant, je n'ai pas réussi à mettre la requête dans une seule variable à cause des apostrophes des CONCAT();.

En tout cas, je vais revoir mes autres requêtes en ayant à l'esprit que je peux utiliser les vues.

Merci encore.