Discussion :

[ebouilleur]

Bonjour,

J'essaye de remonter via une requête mysql tous les scénarios et de remonter 1 seule photo (si il en existe - il peut aussi en exister plusieurs)

Un essaie (infruction) était :

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
 
SELECT distinct s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat as etat_societe, AVG(a1.note_escape) as note_escape, AVG(a1.note_ambiance) as note_ambiance, t2.path, t2.name
			FROM scenario AS s1			
			INNER JOIN societe AS s2 ON (s1.id_societe = s2.id)
			LEFT JOIN file AS f1 ON (s2.id_file=f1.id_file)	  
			LEFT JOIN avis AS a1 ON (s1.id=a1.id_scenario) 
 
			LEFT JOIN (
				SELECT i1.path, i1.name, f2.id_scenario 
				FROM file AS i1
				INNER JOIN scenario_img AS f2 ON (i1.id_file=f2.id_file)	  
				ORDER BY RAND() LIMIT 1
			) t2
			ON t2.id_scenario = s1.id		
			GROUP BY s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat, t2.path, t2.name

Le code fonctionne, mais ne me remonte pas toujours de photo, même quand il en a...
Avez-vous une idée?
Merci

[tatayo]

Bonjour,
Tu nous parles de photos, mais je ne vois pas le terme en question dans la requête.
On ne sait donc pas d'où doit sortir cette photo.
Il nous manque aussi la description des tables, un jeu de test et le résultat attendu.

Tatayo.

[ebouilleur]

Bonsoir,

La photo et son chemin son contenu dans les champs t2.path, t2.name

La table file contient les informations des photos
la table scenario_img sert de jointure entre les id_scenario et id_file

Merci

[tatayo]

Bonjour,
Il nous manque aussi la description des tables, un jeu de test et le résultat attendu.

Tatayo.

[ebouilleur]

Bonsoir,
Voici les tables qui me semblent importantes à partager

Table scenario


Table file


Table scenario_img


Et quand je joue la requête ca me donne (j'ai enlever le champ description pour une meilleur visibilité)


J'ai donc les champs path et name qui sont vides

Et si je fais une requete que vers 1 seul id_scenario, idem j'ai rien dans path et name alors que je sais que j'ai bien des images qui devraient remontrer (enfin 1 seule)

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
 
SELECT distinct s1.id, s1.titre, s1.joueur, s1.niveau, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat as etat_societe, AVG(a1.note_escape) as note_escape, AVG(a1.note_ambiance) as note_ambiance, t2.path, t2.name
			FROM scenario AS s1			
			INNER JOIN societe AS s2 ON (s1.id_societe = s2.id)
			LEFT JOIN file AS f1 ON (s2.id_file=f1.id_file)	  
			LEFT JOIN avis AS a1 ON (s1.id=a1.id_scenario) 
 
			LEFT JOIN (
				SELECT i1.path, i1.name, f2.id_scenario 
				FROM file AS i1
				INNER JOIN scenario_img AS f2 ON (i1.id_file=f2.id_file)	  
				WHERE f2.id = 961
				ORDER BY RAND() LIMIT 1
			) t2
			ON t2.id_scenario = s1.id		
			WHERE s1.id = 961
			GROUP BY s1.id, s1.titre, s1.joueur, s1.niveau, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat, t2.path, t2.name

Merci

[tatayo]

Je pense que tu as un mauvais filtre dans la sous-requête:

• Tu fait en premier S1.id = 960 => c'est donc l'id du scenario
• Ensuite dans la sous-requête => f2.id = 961 : ce n'est pas l'id du scenario, qui ets dans la colonne id_scenario
• Enfin dans la requête principale, la jointure se fair entre t2.id_scenario et s1.id. C'est bien la preuve que le filtre de la sous-requête est faux

Enfin c'est ce qui ressort de l'étude de la requête, mais sans jeu de test il est difficile d'affirmer quoi que ce soit.

Tatayo.

[ebouilleur]

Oui tu as raison, si je me plante dans mes exemples et test je vais pas y arriver !

Donc en en effet la requête suivante :

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
SELECT distinct s1.id, s1.titre, s1.joueur, s1.niveau, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat as etat_societe, AVG(a1.note_escape) as note_escape, AVG(a1.note_ambiance) as note_ambiance, t2.path, t2.name
			FROM scenario AS s1			
			INNER JOIN societe AS s2 ON (s1.id_societe = s2.id)
			LEFT JOIN file AS f1 ON (s2.id_file=f1.id_file)	  
			LEFT JOIN avis AS a1 ON (s1.id=a1.id_scenario) 
 
			LEFT JOIN (
				SELECT i1.path, i1.name, f2.id_scenario 
				FROM file AS i1
				INNER JOIN scenario_img AS f2 ON (i1.id_file=f2.id_file)	  
				WHERE f2.id_scenario = 961
				ORDER BY RAND() LIMIT 1
			) t2
			ON t2.id_scenario = s1.id		
			WHERE s1.id = 961
			GROUP BY s1.id, s1.titre, s1.joueur, s1.niveau, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat, t2.path, t2.name

me donne bien des données pour path et name (et donc le chemin de mon image)

Et dans je supprime les conditions de where, pour ce meme id_scenario je n'ai plus les info d'images...

Qu'entends-tu par jeu de test du coup ?

[tatayo]

Je crois que je comprends.
La sous-requête ne renvoie qu'une seule ligne, triée "au hasard".
Si tu ne filtres pas sur un id de scénario, rien n'indique que cette requête va renvoyer une image pour "le bon" scénario, celui de la requête principale.
Donc si l'Id ne correspond pas, la condition de jointure n'est pas vérifiée, et tu n'as pas d'image.

D'ailleurs il suffit de lancer la sous-requête toute seule pour s'en convaincre.
Il faut utiliser une sous-requête corrélée pour que ça fonctionne.

Tatayo.

[ebouilleur]

Ok, en effet tu as raison
Je regarde sous-requete corrélée, mais je ne vois pas trop la différence avec sous-requete simplement
Et puis ça doit être gourmand comme type de requete j'imagine...

Merci en tout cas

[tatayo]

D'une façon ou d'une autre, il faut bien que tu fasses le lien entre le scénario et le fichier à afficher.
Comme je le disais, là tu récupères un fichier au hasard, mais pas forcément en lien avec le scénario.
Je ne pense pas qu'ajouter un lien entre la sous-requête et la requête principale ait un impact négatif sur les performances, au contraire.
Plutôt que de scanner la table à la recherche d'une ligne au hasard, elle va en premier filtrer sur l'id du scénario, ce qui permet au moteur d'utiliser un index (enfin s'il existe).

Tatayo.

[ebouilleur]

Ok, alors voilà la requête final, qui me semble fonctionnelle :

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
SELECT distinct s1.id, s1.titre, s1.joueur, s1.niveau, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat as etat_societe, AVG(a1.note_escape) as note_escape, AVG(a1.note_ambiance) as note_ambiance, t2.path, t2.name
			FROM scenario AS s1			
			INNER JOIN societe AS s2 ON (s1.id_societe = s2.id)
			LEFT JOIN file AS f1 ON (s2.id_file=f1.id_file)	  
			LEFT JOIN avis AS a1 ON (s1.id=a1.id_scenario) 
 
			LEFT JOIN (
				SELECT i1.path, i1.name, f2.id_scenario 
				FROM file AS i1
				INNER JOIN scenario_img AS f2 ON (i1.id_file=f2.id_file)	  
				WHERE f2.id_scenario IN (
					SELECT id
					FROM scenario
					WHERE scenario.id = f2.id_scenario
					)
				ORDER BY RAND() limit 1
			) t2
			ON t2.id_scenario = s1.id		
			GROUP BY s1.id, s1.titre, s1.joueur, s1.niveau, s1.id_societe, s1.etat, s1.type, s2.nom, s2.adresse, s2.cp, s2.ville, s2.pays, s2.website, s2.etat, t2.path, t2.name

Coté perf, ça donne :
Affichage des lignes 0 - 24 (total de 1629, traitement en 0.0579 seconde(s).)

A titre indicatif, juste l'affichage des scénarios me donne : Affichage des lignes 0 - 24 (total de 1630, traitement en 0.0024 seconde(s).)

Merci pour ton aide Tatayo

[escartefigue]

@ebouilleur : merci de passer le sujet à "résolu" si c'est bien le cas, pour que les autres participants comprennent que la solution proposée est efficiente

[ebouilleur]

je suis pour dire que non ca ne fonctionne pas (je croyais mais en faites non)
donc je continue de chercher

[tatayo]

Mais encore ?
Est-ce que tu peux nous donner un exemple qui "ne fonctionne pas" ?
Quelques lignes avec le résultat attendu et le résultat obtenu, par exemple.

Tatayo.

[ebouilleur]

C'était surtout pour prévenir si des gens passaient et pensaient avoir une solution, je ne pensais pas que tu allais être au taquet
mais merci

en fait je ne peux pas mettre dans ma requête le "ORDER BY RAND() limit 1" sinon ca me limite le retour des image a 1 seule pour toute la liste.
Et si je l'enlève, quand j'ai plusieurs images, ça me crée des doublons :/

[escartefigue]

Si vous ne voulez retenir qu'une seule occurrence parmi n, vous pouvez numéroter chacune avec la fonction OLAP ROW_NUMBER(), puis ne retenir que celle de valeur 1

Attention : applicable seulement avec MySQL V8

[ebouilleur]

Humm peut être oui, même si ca me parait pas la meilleur méthode

Ca me fait penser que peut etre que ca peut le faire avec un having max ou qqchose du genre non?

[escartefigue]

Pas la meilleure méthode ? Pour quelle raison ?
Cette méthode est simple et performante, c'est la raison pour laquelle elle est fréquemment utilisée pour ce genre de besoins.

Voici un exemple d'utilisation simplifié pour seulement deux tables, à adapter à votre cas, et réalisé avec MySQL V8
Une table entête tab1 et une table détail tab2 en lien 0,n avec tab1.

Voici le jeu d'essai réalisé avec des CTE (pour éviter d'avoir à créer les tables et de coder les insert ) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with tab1(t1id, t1c1, t1c2) as
    (select 1, 'TRUC', 'blablabla'   union all
     select 2, 'ABCD', 'coucou'      union all
     select 3, 'ZERT', 'bonjour'     union all
     select 4, 'POIU', 'au revoir'
    )
   , tab2(t1id, t2id, t2c1) as
    (select 1, 1, 'le premier commentaire qui correspond à TRUC'    union all
     select 1, 2, 'le deuxième commentaire qui correspond à TRUC'   union all
     select 1, 3, 'encore un commentaire qui correspond à TRUC'     union all
     select 2, 1, 'un texte correspondant à ABCD'                   union all
     select 4, 1, 'des infos au sujet de POIU'                      union all
     select 4, 2, 'd autres infos au sujet de POIU'
    )

La requête suivante :

Code SQL :

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
select T1.t1id
     , S2.t2id
     , T1.t1c1
     , T1.t1c2
     , S2.t2c1
from tab1 as T1
left join
    (select t1id
          , t2id
          , t2c1
          , row_number()
            over(partition by t1id
                 order by t2id asc) as RN
     from tab2
     ) as S2
  on S2.t1id=T1.t1id
where coalesce(S2.RN, 1)=1

Donne le résultat attendu :

t1id	t2id	t1c1	t1c2	t2c1
1	1	TRUC	blablabla	le premier commentaire qui correspond à TRUC
2	1	ABCD	coucou	un texte correspondant à ABCD
3	null	ZERT	bonjour	null
4	1	POIU	au revoir	des infos au sujet de POIU

[ebouilleur]

Je vais essayé ca alors.

Je me dit que ce n'est pas la meilleur car ça fait récupérer plus de ligne que ce dont j'ai besoin, après c'est juste une réflexion,
merci


Après mes essais, cette requête semble fonctionnelle :

Code sql :

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
 
SELECT distinct s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type, t2.path, t2.name, t2.RN
            FROM scenario AS s1 
            LEFT JOIN (
				SELECT i1.path, i1.name, f2.id_scenario , 
				row_number() over(partition by id_scenario order by id asc) as RN
 
				FROM file AS i1
				INNER JOIN scenario_img AS f2 ON (i1.id_file=f2.id_file)	  
				WHERE f2.id_scenario IN (
					SELECT id
					FROM scenario
					WHERE scenario.id = f2.id_scenario
					)			
 
			) t2 
            ON t2.id_scenario = s1.id 
            WHERE
 
             coalesce(t2.RN, 1)=1
			GROUP BY s1.id, s1.titre, s1.joueur, s1.niveau, s1.description, s1.id_societe, s1.etat, s1.type, t2.path, t2.name

[escartefigue]

la colonne calculée RN (row number) est inutile dans la clause select puisqu'il y a une restriction sur RN=1
et la clause distinct est inutile puisque le regroupement porte sur les mêmes colonnes