Discussion :

[Anduriel]

Bonjour,

Encore une fois, je me frotte aux LEFT OUTER JOIN et GROUP BY, et j'ai encore des problèmes de compréhension...

Dans une première requête, je recherche le nombre de photos présentes dans un album (en vérifiant que l'utilisateur qui a posté existe encore):

Code :

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

1
2
3
4
5
6
7
SELECT pa.name AS nom_album, COUNT(*) AS total_photos
FROM photo_albums pa
INNER JOIN users u ON u.id = pa.link_user_id
LEFT OUTER JOIN photo_images p ON p.link_id_photo_album = pa.id
GROUP BY p.link_id_photo_album
ORDER BY pa.date_added
LIMIT 0 , 3

Ici aucun problème. Maintenant je souhaite récupérer en plus le nombre de commentaires, issus de la table comments:

Code :

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

1
2
3
4
5
6
7
SELECT pa.name AS nom_album, COUNT( pa.id ) AS total_photos, COUNT( c.id ) AS total_comments
FROM photo_albums pa
INNER JOIN users u ON u.id = pa.link_user_id
LEFT OUTER JOIN photo_images p ON p.link_id_photo_album = pa.id
LEFT OUTER JOIN comments c ON c.link_id_item = p.id
GROUP BY p.link_id_photo_album
ORDER BY pa.date_added

Mais là, il me sort n'importe quoi (je ne sais même pas quelles valeurs il prend). Je sais que ça doit provenir du GROUP BY, ou du ON, mais je me perds...

Comment faire ?
Merci

[dehorter olivier]

Dans le GROUP BY doivent au moins figurer les colonnes du SELECT qui ne dérivent pas des fonctions d’agrégations.

Code :

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

1
2
3
4
5
6
7
SELECT pa.name AS nom_album, COUNT(*) AS total_photos
FROM photo_albums pa
INNER JOIN users u ON u.id = pa.link_user_id
LEFT OUTER JOIN photo_images p ON p.link_id_photo_album = pa.id
GROUP BY pa.name, p.link_id_photo_album
ORDER BY pa.date_added
LIMIT 0 , 3

[Waldar]

Mais là, il me sort n'importe quoi (je ne sais même pas quelles valeurs il prend). Je sais que ça doit provenir du GROUP BY, ou du ON, mais je me perds...

Il fait une multiplication entre votre count(pa.id) et count(c.id).
La bonne méthode c'est de faire les agrégats en premier et les jointures ensuite :

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 pa.name                       as nom_album
         , coalesce(p.total_photos  , 0) as total_photos
         , coalesce(c.total_comments, 0) as total_comments
      FROM photo_albums pa
INNER JOIN users u
        ON u.id = pa.link_user_id
 LEFT JOIN ( SELECT link_id_photo_album
                  , count(*) as total_photos
               FROM photo_images
           GROUP BY link_id_photo_album) p
        ON p.link_id_photo_album = pa.id
 LEFT JOIN ( SELECT link_id_item
                  , count(*) as total_comments
               FROM comments
           GROUP BY link_id_item) c
        ON c.link_id_item = p.id
  ORDER BY pa.date_added asc;

[CinePhil]

Dans une première requête, je recherche le nombre de photos présentes dans un album (en vérifiant que l'utilisateur qui a posté existe encore):

Code :

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

1
2
3
4
5
6
7
SELECT pa.name AS nom_album, COUNT(*) AS total_photos
FROM photo_albums pa
INNER JOIN users u ON u.id = pa.link_user_id
LEFT OUTER JOIN photo_images p ON p.link_id_photo_album = pa.id
GROUP BY p.link_id_photo_album
ORDER BY pa.date_added
LIMIT 0 , 3

Ici aucun problème.

Apparemment, la table photo_albums est une table associative qui dit quelle photo est dans quel album. Pourquoi le nom de l'album y figure ?
photo_album serait elle en fait une vue ?

Maintenant je souhaite récupérer en plus le nombre de commentaires, issus de la table comments:

Code :

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

1
2
3
4
5
6
7
SELECT pa.name AS nom_album, COUNT( pa.id ) AS total_photos, COUNT( c.id ) AS total_comments
FROM photo_albums pa
INNER JOIN users u ON u.id = pa.link_user_id
LEFT OUTER JOIN photo_images p ON p.link_id_photo_album = pa.id
LEFT OUTER JOIN comments c ON c.link_id_item = p.id
GROUP BY p.link_id_photo_album
ORDER BY pa.date_added

Mais là, il me sort n'importe quoi (je ne sais même pas quelles valeurs il prend). Je sais que ça doit provenir du GROUP BY, ou du ON, mais je me perds...

Apparemment, tu cherches maintenant à compter en plus les commentaires sur les photos et non pas les commentaires sur les albums.

Explication du phénomène...

Soit les données suivantes dans photo_albums, en me limitant aux identifiants :
album / photo
1 / 12
1 / 25
1 / 26
2 / 4
2 / 8
2 / 12
2/ 32

Combien de photos par album ?
On compte le nombre de lignes dans la table pour chaque identifiant d'album.
1 => 3 lignes donc 3 photos
2 => 4 lignes donc 4 photos

Maintenant imaginons la jointure avec les commentaires sur les photos. Je mets des textes de commentaires pour que ce soit plus clair :
album / photo / commentaire
1 / 12 / NULL
1 / 25 / 'Que c'est beau !'
1 / 25 / 'Ouais... pas mal !
1 / 26 / 'LoL !'
2 / 4 / 'Beurk ! Qu'il est moche celui-là ! '
2 / 4 / 'C'est monstrueux !'
2 / 4 / 'Il paraît qu'il y en a qui en mangent !'
2 / 8 / NULL
2 / 12 / 'Joli !'
2/ 32 / 'Bravo !'

Si je compte de nouveau les lignes pour chaque album, j'obtiens :
1 => 4, alors qu'il n'y a que 3 photos !
2 => 6, alors qu'il n'y a que 4 photos !

Il faut compter les identifiants DISTINCT des photos et des commentaires :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT pa.name AS nom_album, 
	COUNT( DISTINCT pa.id ) AS total_photos, 
	COUNT( DISTINCT c.id ) AS total_comments
FROM photo_albums pa
INNER JOIN users u ON u.id = pa.link_user_id
LEFT OUTER JOIN photo_images p ON p.link_id_photo_album = pa.id
	LEFT OUTER JOIN comments c ON c.link_id_item = p.id
GROUP BY pa.name
ORDER BY pa.date_added

Le second DISTINCT n'est pas utile puisque c'est la plus petite division du résultat mais si vous voulez compter encore autre chose, par exemple les réponses aux commentaires, c'est le même principe.

[Anduriel]

Merci beaucoup pour toutes vos réponses !

[dehorter olivier]

Une question pour waldar et cinephil

Il fait une multiplication entre votre count(pa.id) et count(c.id).
La bonne méthode c'est de faire les agrégats en premier et les jointures ensuite :

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 pa.name                       as nom_album
         , coalesce(p.total_photos  , 0) as total_photos
         , coalesce(c.total_comments, 0) as total_comments
      FROM photo_albums pa
INNER JOIN users u
        ON u.id = pa.link_user_id
 LEFT JOIN ( SELECT link_id_photo_album
                  , count(*) as total_photos
               FROM photo_images
           GROUP BY link_id_photo_album) p
        ON p.link_id_photo_album = pa.id
 LEFT JOIN ( SELECT link_id_item
                  , count(*) as total_comments
               FROM comments
           GROUP BY link_id_item) c
        ON c.link_id_item = p.id
  ORDER BY pa.date_added asc;

Comment ce fait-il que cette requête soit acceptée alors que la colonne pa.name as nom_album ne se trouve pas dans le ORDER BY ?

idem pour les requêtes d'exemple d'Anduriel ???

[Waldar]

Il ne faut pas confondre ORDER BY et GROUP BY.

On peut parfaitement trier sur une colonne qui n'est pas affichée dans le select.
Il y a des restrictions quand la requête comporte un DISTINCT ou un agrégat.

Une remarque pour Cinephil : mélanger photo et commentaire créé un produit cartésien : de facto la requête avec le count(distinct) reverra le bon résultat, mais le moteur devra quand même calculer ce produit cartésien, et ça peut mettre très vite le serveur à genou.

De plus si on désire une autre métrique (la somme de jenesaisquoi), le distinct n'a plus de sens alors que l'agrégat avant jointure lui ne pose pas de problème.

[al1_24]

Il est possible d'utiliser dans la clause ORDER BY n'importe quelle colonne présente dans les tables, ou tables dérivées, constitutives de cette requête, même si cette colonne n'apparait pas dans la clause SELECT.

[dehorter olivier]

mais quel con