sous requête avec group_concat

[gtraxx]

Bonjour, je suis entrain de préparer mes requêtes et je souhaite savoir si on peut utiliser group_concat dans une sous requête.
Je m'explique :
Je dois récupérer une série d'enregistrement dans une table et récupérer plusieurs valeur dans une autre avec un group_concat et faire une séparation par une virgule.
Mais j'avoue que group_concat ne fais pas partie des fonctions les plus utilisé chez moi (mais j'aime bien me prendre la tête de temps en temps).

Code :

1
2
3
4
5
6
7
 
SELECT m.idmovies,m.m_name,f.m_f_name,lang.iso,(SELECT GROUP_CONCAT(idkind_rel SEPARATOR ',' ORDER BY idkind) AS LIST_USERS FROM mg_movies_kind_rel 
WHERE m.idmovies)
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
JOIN mg_language AS lang ON(m.idlang = lang.idlang) 
WHERE lang.idlang = 1 AND m.m_name LIKE "%mon%" ORDER BY m.idmovies DESC

Je récupère des enregistrement dans la table mg_movies avec deux jointures nécessaire pour avoir tout ce dont j'ai besoin.
mais je souhaite en même temps récupérer la liste des mots clés dans une autre table qui doit être séparer par une virgule.
Je pense m'être planter dans la syntaxe ou peut être la manière de procéder

[Maljuna Kris]

Saluton,
Je reste très perplexe quant à la pertinence de la démarche, mais la syntaxe de la requête me semble correcte.
Quel est le problème, MySQL refuse-t-il l'emploi de GROUP_CONCAT en l'absence d'une clause GROUP BY ?

[gtraxx]

Bonjour, je sais que je peux sans problème séparer sa en deux requêtes ce que je fais habituellement.
Mais c'est surtout pour tester et voir si c possible dans un tel cas d'utiliser group_concat
Voila le message d'erreur

Code :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY idkind) AS LIST_USERS FROM mg_movies_kind_rel WHERE m.idmovies) FROM m' at line 1

Je reste très perplexe quant à la pertinence de la démarche

Je tente de faire fonctionner sa mais j'avoue que je ne sais pas si c très optimisé voir recommandé

[punkoff]

la sous-requête

Code :

1
2
3
 
SELECT GROUP_CONCAT(idkind_rel SEPARATOR ',' ORDER BY idkind) AS LIST_USERS FROM mg_movies_kind_rel 
WHERE m.idmovies

n'est pas bonne.

ensuite j'ai l'impression qu'avec la bonne syntaxe ca donnerai ceci :

Code :

1
2
3
4
5
6
7
 
SELECT m.idmovies,m.m_name,f.m_f_name,lang.iso,(SELECT GROUP_CONCAT(idkind_rel SEPARATOR ',' ORDER BY idkind) AS LIST_USERS FROM mg_movies_kind_rel rel
WHERE  m.idmovies = rel.id_quelquechose)
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
JOIN mg_language AS lang ON(m.idlang = lang.idlang) 
WHERE lang.idlang = 1 AND m.m_name LIKE "%mon%" ORDER BY m.idmovies DESC

Ce genre de statement est à proscrire, sauf si vous recherchez que très peu de ligne résultat (là ca sera performant , mais il manquera peut-être un group by à tester..)


Dans le cas où vos conditions ramènes beaucoup de ligne il faudrai plutot passer par une phase de consolidation et de joindre ceci via left outer join par exemple :

Code :

1
2
3
4
5
6
 
SELECT ....
FROM mg_movies AS m
LEFT OUTER JOIN (SELECT rel.id_quelquechose
, GROUP_CONCAT(idkind_rel SEPARATOR ',' ORDER BY idkind) AS LIST_USERS FROM mg_movies_kind_rel rel GROUP BY rel.id_quelquechose)  rel ON rel.id_quelquechose = m.idmovies 
.....

testez avec votre volumétrie

[Maljuna Kris]

Bien vu, punkoff, WHERE m.idmovies peut-être logiquement correct (m.idmovies serait testé comme un booléen) mais est syntaxiquement incomplet.

[gtraxx]

Merci punkoof, j'ai tester avec les deux type histoire de me faire la main:
Première méthode:

Code :

1
2
3
4
5
6
7
 
SELECT m.idmovies,m.m_name,f.m_f_name,lang.iso,(SELECT GROUP_CONCAT(rel.idkind_rel ORDER BY rel.idkind SEPARATOR ',') AS LIST_KIND FROM mg_movies_kind_rel rel
WHERE rel.idmovies = m.idmovies) AS WORD_LIST_KIND
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
JOIN mg_language AS lang ON(m.idlang = lang.idlang) 
WHERE lang.idlang = 1 AND m.m_name LIKE "%mon%" ORDER BY m.idmovies DESC

Pas d'erreur mais me retourne un type blob pour WORD_LIST_KIND
Deuxième méthode avec phase de consolidation:

Code :

1
2
3
4
5
6
7
8
 
SELECT m.idmovies,m.m_name,f.m_f_name,lang.iso
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
LEFT OUTER JOIN (SELECT idkind
, GROUP_CONCAT(idkind_rel ORDER BY idkind SEPARATOR ',') AS LIST_USERS FROM mg_movies_kind_rel rel GROUP BY rel.idmovies) rel ON(rel.idmovies = m.idmovies)
JOIN mg_language AS lang ON(m.idlang = lang.idlang) 
WHERE lang.idlang = 1 AND m.m_name LIKE "%mon%" ORDER BY m.idmovies DESC

J'ai une erreur :

#1054 - Unknown column 'rel.idmovies' in 'on clause'

[punkoff]

relisez votre sous-requête, en particulier le select

[gtraxx]

ok c'est fait :

Code :

1
2
3
4
5
6
7
SELECT m.idmovies,m.m_name,f.m_f_name,lang.iso,rel.WORD_LIST_KIND
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
LEFT OUTER JOIN (SELECT rel.idkind,rel.idmovies
, GROUP_CONCAT(idkind_rel ORDER BY idkind SEPARATOR ',') AS WORD_LIST_KIND FROM mg_movies_kind_rel rel GROUP BY rel.idmovies) rel ON(rel.idmovies = m.idmovies)
JOIN mg_language AS lang ON(m.idlang = lang.idlang) 
WHERE lang.idlang = 1 AND m.m_name LIKE "%mon%" ORDER BY m.idmovies DESC

Sa fonctionne mais j'ai un type blob c'est normal ?
Sinon j'ai une dernière question la table mg_movies_kind_rel doit être lié avec la table mg_movies_kind_helper afin de retourner les mots avec une jointure sur l'identifiant, je suppose que faire une jointure supplémentaire dans la sous requête c'est ok ?

[punkoff]

tout est dans la doc :http://dev.mysql.com/doc/refman/5.5/...n_group-concat

Concernant votre sous-requete, esssayez ?

[gtraxx]

Voila je viens de modifier la requête en incluant ma jointure :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT m.idmovies,m.m_name,f.m_f_name,lang.iso,rel.WORD_LIST_KIND
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
LEFT OUTER JOIN (SELECT rel.idmovies
, GROUP_CONCAT(h.m_k_name ORDER BY idkind SEPARATOR ',') AS WORD_LIST_KIND 
FROM mg_movies_kind_rel rel
JOIN mg_movies_kind_helper AS h USING(idkind)
GROUP BY rel.idmovies) rel ON(rel.idmovies = m.idmovies)
JOIN mg_language AS lang ON(m.idlang = lang.idlang) 
WHERE lang.idlang = 1 AND m.m_name LIKE "%machin%" ORDER BY m.idmovies DESC

Sa fonctionne par contre je m'interroge toujours sur la première méthode, elle est censé retournée plus ou moins la même chose (donc pas en blob)
je vais essayer de creuser sa en tout cas grand merci, il ma fallut du temps pour comprendre

[punkoff]

Les deux requêtes doivent ramener le même résultat.

La différence entre les deux est que la 1ere est une requete scalaire, cad qu'elle va effectuer autant de fois la sous-requête que vosu avez de ligne retourner par la requête principale.

Au vu de la jointure c'est assez performant pour quelque ligne retournée, mais ca va mettre à plat le SGBD assez vite si le nombre de ligne retourné est grand.


La deuxième requête, logiquement va d'abord construire la sous-requête pusi va joindre ces résultat avec la requête principale.
Du coup vous perdez un temps fixe mais qui n'augmentera pas avec la volumétrie retournée

Bon sous MySql il faudrai vérifier les plans d'execution mais ça devrait être comme ceci.

[gtraxx]

Un test sous forme de requête scalaire :

Code :

1
2
3
4
5
6
7
SELECT m.idmovies,m.m_name,f.m_f_name,lang.iso,(SELECT GROUP_CONCAT(h.m_k_name ORDER BY idkind SEPARATOR ',') AS LIST_KIND FROM mg_movies_kind_rel rel
JOIN mg_movies_kind_helper AS h USING(idkind)
WHERE rel.idmovies = m.idmovies) AS WORD_LIST_KIND
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
JOIN mg_language AS lang ON(m.idlang = lang.idlang) 
WHERE lang.idlang = 1 AND m.m_name LIKE "%machin%" ORDER BY m.idmovies DESC

Sa fonctionne également, je vais plutôt utiliser la deuxième méthode histoire de ne pas flinguer le SGBD car vu le nombre de résultat je pense que c'est bien mieux.
Grand merci, j'en sais un peu plus désormais sur l'utilisation de group_concat et des sous requêtes de ce type

[CinePhil]

Si je comprends bien le sens de la requête, la liste de mots vient de mg_movies_kind_helper qui est associé à mg_movies_kind_rel qui est associé à mg_movies.

Pourquoi ne pas faire tout simplement les jointures nécessaires sans sous-requête et avec un GROUP BY général ?

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT m.idmovies, m.m_name, f.m_f_name, lang.iso, 
	GROUP_CONCAT(h.m_k_name ORDER BY idkind SEPARATOR ',') AS WORD_LIST_KIND
FROM mg_movies AS m
JOIN mg_movies_format AS f USING(idformat)
JOIN mg_language AS lang ON m.idlang = lang.idlang 
JOIN mg_movies_kind_rel rel ON rel.idmovies = m.idmovies
	JOIN mg_movies_kind_helper AS h ON h.idkind = rel.idkind
WHERE lang.idlang = 1 
	AND m.m_name LIKE "%machin%" 
GROUP BY m.idmovies, m.m_name, f.m_f_name, lang.iso
ORDER BY m.idmovies DESC

Cela ne donne t-il pas le même résultat ?

[Maljuna Kris]

Sur le principe, je pense que tu as raison Cinephil, cependant la jointure sur sous-requête sera plus performante car le GROUP_CONCAT et l'ORDER BY associé n'y seront exécutés qu'une seule fois.

[CinePhil]

GROUP_CONCAT est une fonction de groupage non ? Donc à utiliser avec GROUP BY normalement non ?

La requête dans le SELECT est une requête corrélée non ? Une requête corrélée serait plus performante qu'une jointure ?

Je pense au contraire que la sous-requête dans le SELECT va être exécutée autant de fois qu'il y a de lignes de résultat (autant de fois qu'il y a de films à retourner par la requête) alors qu'avec le GROUP BY, l'opération se fait en une seule fois au fur et à mesure de l'examen des lignes. Non ?

[Maljuna Kris]

Envoyé par CinePhil

La requête dans le SELECT est une requête corrélée non ?

Ben justement, non. Elle crée juste une table temporaire en mémoire qui ne contiendra qu'une ligne puisqu'elle ne contient pas de GROUP BY et qui, de mon point de vue, ne sera exécuté qu'une fois pour permettre la réalisation de la jointure.
C'était d'ailleurs l'avis de punkoff

La deuxième requête, logiquement va d'abord construire la sous-requête pusi va joindre ces résultat avec la requête principale.
Du coup vous perdez un temps fixe mais qui n'augmentera pas avec la volumétrie retournée

que je partage encore assez

[punkoff]

Bah testez y en a pour 5min, perso j'ai pas mysql de dispo.

La requête de Cinephil me plaie bien aussi !

edit :

La requête dans le SELECT est une requête corrélée non ?

Pour moi oui.

En fait je penses que vous êtes d'accord mais que vous ne parlez pas de la même requête :p

[Maljuna Kris]

Envoyé par punkoff

En fait je penses que vous êtes d'accord mais que vous ne parlez pas de la même requête :p

Ça, c'est tout à fait possible, vu mon âge avancé et mon état de grande fatigue.
Cependant, selon moi

Code mySQL :

1
2
(SELECT rel.idmovies
, GROUP_CONCAT(idkind_rel ORDER BY idkind SEPARATOR ',') AS LIST_USERS FROM mg_movies_kind_rel rel GROUP BY rel.idmovies)

il n'ya aucune corrélation entre cette sous-requête et la requête qui l'englobe.
D'ailleurs à quoi bon puisque la "corrélation" est introduite par la clause de jointure

Code mySQL :

ON rel.idmovies = m.idmovies

[punkoff]

Envoyé par Maljuna Kris

[CODE=mySQL](SELECT rel.idmovies
, GROUP_CONCAT(idkind_rel ORDER BY idkind SEPARATOR ',') AS LIST_USERS FROM mg_movies_kind_rel rel GROUP BY rel.idmovies)[/CODE

]il n'ya aucune corrélation entre cette sous-requête et la requête qui l'englobe.
D'ailleurs à quoi bon puisque la "corrélation" est introduite par la clause de jointure

Code mySQL :

ON rel.idmovies = m.idmovies

oui, il n'y a aucune correlation ici.


Par contre Cinephil parlais de l'autre requête avec le sous select dans le select, et là la il y a corrélation !

[Maljuna Kris]

Envoyé par punkoff

oui, il n'y a aucune correlation ici.


Par contre Cinephil parlais de l'autre requête avec le sous select dans le select, et là la il y a corrélation !

Oula, les jeunes, vous m'embrouillez avec toutes ces versions.
J'en suis au 36° dessous.