Obtenir un tableau sans utiliser GROUP_CONCAT

dedis · 10/04/2011, 18h31

Bonjour,

je suis face à une situation plus que classique : j'ai deux tables A et B associées l'une à l'autre par une table de relation a_has_b .

Un élément de A est associé à plusieurs lignes dans B.

Mon objectif est pour une ligne donnée de A, obtenir cette ligne plus l'ensemble des champs `noms` des lignes de B correspondantes concaténés entre eux et séparés par une virgule.

Ex :

----------------------
| id | b_names |
----------------------
| 10 | toto, tata |
----------------------

Ce problème étant j'imagine très courant, je suppose qu'il doit y avoir un pattern pour le résoudre non

?

Voilà la manière dont je procède :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT a.*, d.names as b_names
FROM a 
LEFT JOIN (
SELECT GROUP_CONCAT( name ) AS NAMES, ida 
FROM (
SELECT b.name AS name, ahb.a_ida as ida
FROM a_has_b AS ahb
LEFT JOIN b ON b.idb = ahb.b_idb
WHERE a_ida = 10
) AS list) AS d on d.ida = a.ida
WHERE a.ida = 10

Cela me semble bien compliqué...

Y-a-til une solution plus élégante ?

ced · 10/04/2011, 21h09

Bonjour,

Citation:

Envoyé par dedis

Y-a-til une solution plus élégante ?

Oui. La solution la plus élégante, et la plus logique, consiste à ne pas utiliser le SQL pour tous ces problèmes de cosmétique des données (le SQL n'est pas fait pour ça), mais à utiliser le langage qui interroge la base pour réaliser ces opérations (PHP par exemple).

C'est effectivement une question récurrente, à laquelle un tutoriel apporte des réponses très pertinentes : http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L9

ced

dedis · 10/04/2011, 23h23

ok merci beaucoup pour cette réponse précise, je vais prendre le temps de lire la page en entier de manière attentive.

En fait, j'étais convaincu de bien faire en m'évertuant à écrire des requêtes complexes pour en limiter au maximum le nombre

. J'ai cru comprendre que du point de vu de la performance (qui est devenu un critère primordial dans les problématiques de référencement), plus le nombre de requêtes est limité plus le site est réactif.

Or, la situation que je présente est courante lors de la réalisation de site Web, ne serait-ce par exemple que pour joindre des pages et des catégories. Il me semble que si je voulais ré-écrire mes éléments de 'cosmétiques' en PHP, je serais obligé de casser les sous requêtes en une multitude de petites requêtes indépendantes... J'ai tenté de regarder dans des CMS populaires la manière dont le problème est traité, mais l'utilisation de plus en plus répandue d'ORM rend tout ça assez illisible

.

Existe-t-il un moyen de passer la cosmétique côté PHP en ne faisant qu'une seule requête ?
Le temps gagné par l'utilisation d'une requête unique est-il réel (ie: si le SGBD met plus temps que PHP à faire la cosmétique... c peut-être simplement contre-productif)?

dedis · 10/04/2011, 23h44

bon désolé de flooder, mais je viens de lire en détail la question sur la cosmétique et les choses sont beaucoup plus claires...

Citation:

Comme on le voit, la première requête donne les même résultats que la dernière... Seule la présentation à changée....

Dans la recherche de la requête complexe donnée dans mon premier post, j'ai en effet d'abord obtenu une première requête simple me donnant un résultat "peu susceptible de plaire aux commerciaux"

. Cette requête unique suffira, fin du problème.

Je suis un peu fatigué là, je la poserais demain et mettrais le sujet en résolu. Merci encore

dedis · 11/04/2011, 11h54

Bon, c fait. Voilà la requête :

Code :

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

1
2
3
4
5
6
7
SELECT *
FROM s
     LEFT JOIN (SELECT *
                FROM  shr
                        LEFT JOIN  r ON r.idr = shr.r_idr
                ) AS res ON res.ids = s.ids
WHERE s.ids = 10

Qui me retourne bien plusieurs lignes qui ne 'plaisent pas aux commerciaux'

mais contenant bien toutes les infos requises (en vrai je ne mets pas * mais je liste les colonnes qui m'intéresse).

Côté PHP :

Code :

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

1
2
3
4
5
6
7
foreach($results as $result)
		{
		    $row['col1']=$result['col1'];
		    ...
		    $row['names'][]=$result['name'];
		}
return $row;

et là, je suis moins sûr... ça marche, mais encore une fois, est-ce élégant ?
Pour chaque ligne j'écrase les colonnes ayant les mêmes infos, et je place dans un tableau celle qui varie. Comment auriez vous fait ?

ced · 11/04/2011, 18h13

Là, ça devient plus un problème de PHP...

ced

stealth35 · 11/04/2011, 18h16

avec PDO, tu peux faire ça très facilement (et proprement) grâce au FETCH_GROUP

J'ai mis un exemple ici :

Citation:

exemple :

Citation:

+----+-----------+----------+-------+
| id | firstname | lastname | city |
+----+-----------+----------+-------+
| 1 | Jean | Machin | Paris |
| 2 | Jean | Truc | Nice |
| 3 | Paul | Bla | Nice |
+----+-----------+----------+-------+

on a envie de trier les utilisateur par ville pour avoir une sortie type :

Tout sélectionner

Citation:

Paris
Jean Machin
Nice
Jean Truc
Paul Bla

plusieurs solutions :

Faire une requête DISTINCT sur les villes, et a chaque tour de boucle allé chercher les personnes qui correspondes
Créer un array temporaire et a chaque tour de boucle ajouter la personne ($tmp[$user['city']][] = $user) , et reboucler ensuite pour créer son tableau
A chaque tour de boucle vérifier via un buffer si la ville a été mise et afficher les personnes, sinon afficher ville

la solution PDO :

Grâce au fetchAll et son option PDO::FETCH_GROUP, on a en sortie directement un array formateé, avec en clé la catégorie et en valeur les entrées correspondantes
(par contre attention contraiement a PDO::FETCH_COLUMN, on ne choisie pas qui est la catégorie, ca sera uniquement la première colonne)

exemple de code :

Code php :

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
try
{
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'root');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
}
catch (Exception $e)
{
    exit($e->getMessage());
}
 
$query = $pdo->query("SELECT u.city, u.id, u.firstname, u.lastname FROM test_user u");
 
if($query)
{
    $result = $query->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
    print_r($result);
}

en sortie

Tout sélectionner

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
Array
(
    [Paris] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [firstname] => Jean
                    [lastname] => Machin
                )
 
        )
 
    [Nice] => Array
        (
            [0] => Array
                (
                    [id] => 2
                    [firstname] => Jean
                    [lastname] => Truc
                )
 
            [1] => Array
                (
                    [id] => 3
                    [firstname] => Paul
                    [lastname] => Bla
                )
 
        )
 
)

tout simplement

Si on veux que la catégorie sois un autre champs il suffis de le mettre en premier (le prénom par exemple)

Code sql :

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

SELECT u.firstname, u.id, u.lastname, u.city FROM test_user u

Tout sélectionner

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
Array
(
    [Jean] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [lastname] => Machin
                    [city] => Paris
                )
 
            [1] => Array
                (
                    [id] => 2
                    [lastname] => Truc
                    [city] => Nice
                )
 
        )
 
    [Paul] => Array
        (
            [0] => Array
                (
                    [id] => 3
                    [lastname] => Bla
                    [city] => Nice
                )
 
        )
 
)

et on peux bien sur doubler les valeurs pour avoir des entrées complètes, ce qui sera d'ailleurs plus simple, au niveau portabilité et pour différentes sorties

Code sql :

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

SELECT u.city, u.id, u.firstname, u.lastname, u.city FROM test_user u

-- ou version raccourcis

Code sql :

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

SELECT u.city, u.* FROM test_user u

Tout sélectionner

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
Array
(
    [Paris] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [firstname] => Jean
                    [lastname] => Machin
                    [city] => Paris
                )
 
        )
 
    [Nice] => Array
        (
            [0] => Array
                (
                    [id] => 2
                    [firstname] => Jean
                    [lastname] => Truc
                    [city] => Nice
                )
 
            [1] => Array
                (
                    [id] => 3
                    [firstname] => Paul
                    [lastname] => Bla
                    [city] => Nice
                )
 
        )
 
)

dedis · 11/04/2011, 18h22

Citation:

Envoyé par stealth35

avec PDO, tu peux faire ça très facilement (et proprement) grâce au FETCH_GROUP

J'ai mis un exemple ici :

Citation:

exemple :

Citation:

+----+-----------+----------+-------+
| id | firstname | lastname | city |
+----+-----------+----------+-------+
| 1 | Jean | Machin | Paris |
| 2 | Jean | Truc | Nice |
| 3 | Paul | Bla | Nice |
+----+-----------+----------+-------+

on a envie de trier les utilisateur par ville pour avoir une sortie type :

Tout sélectionner

Citation:

Paris
Jean Machin
Nice
Jean Truc
Paul Bla

plusieurs solutions :

Faire une requête DISTINCT sur les villes, et a chaque tour de boucle allé chercher les personnes qui correspondes
Créer un array temporaire et a chaque tour de boucle ajouter la personne ($tmp[$user['city']][] = $user) , et reboucler ensuite pour créer son tableau
A chaque tour de boucle vérifier via un buffer si la ville a été mise et afficher les personnes, sinon afficher ville

la solution PDO :

Grâce au fetchAll et son option PDO::FETCH_GROUP, on a en sortie directement un array formateé, avec en clé la catégorie et en valeur les entrées correspondantes
(par contre attention contraiement a PDO::FETCH_COLUMN, on ne choisie pas qui est la catégorie, ca sera uniquement la première colonne)

exemple de code :

Code php :

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
try
{
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'root');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
}
catch (Exception $e)
{
    exit($e->getMessage());
}
 
$query = $pdo->query("SELECT u.city, u.id, u.firstname, u.lastname FROM test_user u");
 
if($query)
{
    $result = $query->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
    print_r($result);
}

en sortie

Tout sélectionner

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
Array
(
    [Paris] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [firstname] => Jean
                    [lastname] => Machin
                )
 
        )
 
    [Nice] => Array
        (
            [0] => Array
                (
                    [id] => 2
                    [firstname] => Jean
                    [lastname] => Truc
                )
 
            [1] => Array
                (
                    [id] => 3
                    [firstname] => Paul
                    [lastname] => Bla
                )
 
        )
 
)

tout simplement

Si on veux que la catégorie sois un autre champs il suffis de le mettre en premier (le prénom par exemple)

Code sql :

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

SELECT u.firstname, u.id, u.lastname, u.city FROM test_user u

Tout sélectionner

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
Array
(
    [Jean] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [lastname] => Machin
                    [city] => Paris
                )
 
            [1] => Array
                (
                    [id] => 2
                    [lastname] => Truc
                    [city] => Nice
                )
 
        )
 
    [Paul] => Array
        (
            [0] => Array
                (
                    [id] => 3
                    [lastname] => Bla
                    [city] => Nice
                )
 
        )
 
)

et on peux bien sur doubler les valeurs pour avoir des entrées complètes, ce qui sera d'ailleurs plus simple, au niveau portabilité et pour différentes sorties

Code sql :

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

SELECT u.city, u.id, u.firstname, u.lastname, u.city FROM test_user u

-- ou version raccourcis

Code sql :

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

SELECT u.city, u.* FROM test_user u

Tout sélectionner

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
Array
(
    [Paris] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [firstname] => Jean
                    [lastname] => Machin
                    [city] => Paris
                )
 
        )
 
    [Nice] => Array
        (
            [0] => Array
                (
                    [id] => 2
                    [firstname] => Jean
                    [lastname] => Truc
                    [city] => Nice
                )
 
            [1] => Array
                (
                    [id] => 3
                    [firstname] => Paul
                    [lastname] => Bla
                    [city] => Nice
                )
 
        )
 
)

pour tout dire je fais ça dans Zend avec zend_db_table sur PDO. Mais ce qui m'intéresse c la tête des requêtes générées et savoir si c 'propre'. Visiblement ça ne l'était pas.

Pour des problématiques aussi basiques que celles-là, j'aimerais savoir quelles sont les bonnes pratiques.

stealth35 · 11/04/2011, 18h27

Citation:

Envoyé par dedis

pour tout dire je fais ça dans Zend avec zend_db_table sur PDO. Mais ce qui m'intéresse c la tête des requêtes générées et savoir si c 'propre'. Visiblement ça ne l'était pas.

Pour des problématiques aussi basiques que celles-là, j'aimerais savoir quelles sont les bonnes pratiques.

non c'est pas une bonne pratique, une jointure suffis

dedis · 11/04/2011, 18h28

Citation:

Envoyé par stealth35

non c'est pas une bonne pratique, une jointure suffis

Qu'est-ce qui n'est pas une bonne pratique ? Une jointure suffit pour quoi ?

stealth35 · 11/04/2011, 18h40

Citation:

Envoyé par dedis

Qu'est-ce qui n'est pas une bonne pratique ? Une jointure suffit pour quoi ?

de faire un groupe concat

dedis · 11/04/2011, 18h53

Citation:

Envoyé par stealth35

de faire un groupe concat

lol. ok désolé.

En fait, ce problème là est résolu puisque ced m'a expliqué que la cosmétique n'avait pas sa place côté SQL (et j'imagine donc que FETCH_GROUP de PDO rentre dans ce cadre).

J'ai donc fait une nouvelle requête SQL, la plus simple que possible, et passé la cosmétique côté PHP avec :

Code :

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

1
2
3
4
5
6
7
foreach($results as $result)
		{
		    $row['col1']=$result['col1'];
		    ...
		    $row['names'][]=$result['name'];
		}
return $row;

Ce qui marche sans problème mais me paraît toujours un peu crado (l'écrasement des données notamment).

Encore une fois, comme il s'agit d'un problème très basique et très récurent, ce que j'aimerais savoir c'est quelle est la bonne pratique pour ce problème.

Il doit bien y avoir une solution type ultra-propre recommandées et utilisée par tous non ? Un peu comme pour les pattern en POO ?

stealth35 · 11/04/2011, 19h03

euh oui avec le FETCH_GROUP...

10/04/2011, 23h23	#3
dedis Futur Membre du Club Inscription : mai 2008 Messages : 52 Détails du profil Informations forums : Inscription : mai 2008 Messages : 52 Points : 15 Points : 15	ok merci beaucoup pour cette réponse précise, je vais prendre le temps de lire la page en entier de manière attentive. En fait, j'étais convaincu de bien faire en m'évertuant à écrire des requêtes complexes pour en limiter au maximum le nombre . J'ai cru comprendre que du point de vu de la performance (qui est devenu un critère primordial dans les problématiques de référencement), plus le nombre de requêtes est limité plus le site est réactif. Or, la situation que je présente est courante lors de la réalisation de site Web, ne serait-ce par exemple que pour joindre des pages et des catégories. Il me semble que si je voulais ré-écrire mes éléments de 'cosmétiques' en PHP, je serais obligé de casser les sous requêtes en une multitude de petites requêtes indépendantes... J'ai tenté de regarder dans des CMS populaires la manière dont le problème est traité, mais l'utilisation de plus en plus répandue d'ORM rend tout ça assez illisible . Existe-t-il un moyen de passer la cosmétique côté PHP en ne faisant qu'une seule requête ? Le temps gagné par l'utilisation d'une requête unique est-il réel (ie: si le SGBD met plus temps que PHP à faire la cosmétique... c peut-être simplement contre-productif)?
	00

11/04/2011, 18h13	#6
ced Rédacteur/Modérateur Cédric Duprez Inscription : avril 2002 Messages : 3 823 Détails du profil Informations personnelles : Nom : Cédric Duprez Âge : 36 Localisation : France, Loiret (Centre) Informations professionnelles : Secteur : Agroalimentaire - Agriculture Informations forums : Inscription : avril 2002 Messages : 3 823 Points : 6 440 Points : 6 440	Là, ça devient plus un problème de PHP... ced __________________ Rédacteur / Modérateur SGBD Mes tutoriels et la FAQ MySQL ---------------------------------------------------- Pensez aux balises code et au tag Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça
	00

11/04/2011, 19h03	#13
stealth35 Modérateur Inscription : septembre 2010 Messages : 7 103 Détails du profil Informations forums : Inscription : septembre 2010 Messages : 7 103 Points : 8 466 Points : 8 466	euh oui avec le FETCH_GROUP... __________________ http://blog.stealth35.com/
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email