[MySQL] count() avec JOIN

[p4bl0]

hello !

J'ai un petit souci j'arrive pas à faire un truc en SQL :

Je cherche à compter le nombre de commentaires correspondant à un post, voici ma requête (elle est simplifié, là elle récupère juste le titre et le nombre de commentaires des posts, mais ça suffit pour mon problème) :

SELECT `posts`.`title`, count(`comments`.`id`) AS `nb_comments`
FROM `posts`
JOIN `comments`
ON `comments`.`pid`=`posts`.`id`
WHERE `posts`.`status`>3
GROUP BY `posts`.`id`;

Le problème c'est que si un post n'a pas de commentaire la requête ne le récupère pas.
le comportement que je désirerais obtenir c'est que ça récupère aussi les posts qui n'ont pas de commentaire avec nb_comments qui vaut 0 ou NULL ou même n'importe quoi qui ne soit pas un entier positif .


Je vois vraiment pas comment faire mais ça doit être possible, sauf que je ne m'y connais pas encore assez en SQL :hein:

 

[p4bl0]

C'est bon j'ai trouvé !!

En fait c'est beaucoup plus simple d'utiliser une subquery, j'y avais pas pensé je galérais depuis bien une heure j'en pouvais plus


alors voilà faut faire une truc du genre (pour la version simplifier) :

SELECT `posts`.`title`, (SELECT count(`comments`.`id`) FROM `comments` WHERE `comments`.`pid`=`posts`.`id`) AS `nb_comments`
FROM `posts`
WHERE `posts`.`status`>3;

Ça évite le GROUP BY en plus vu que le count() est dans la subquery
(j'ai toujours le JOIN par contre dans la vraie requête, mais avec une autre table ).



EDIT: une autre question au passage vous savez si MySQL peut retourner un tableau en PHP ?
Genre est ce que dans la même requête je peux récupérer une liste de tag présent dans une autre table (avec une table de jointure post.id et tag.id) ?
Sinon je peut toujours le faire en plusieurs requêtes (une par post du coup pour les tags....)

 

[grumff]

Pour le contage des commentaires je te conseille fortement d'ajouter un champ dans ta table de posts que tu mets à jour chaque fois qu'un commentaire est ajouté ou supprimé (par exemple à l'aide d'un trigger). C'est beaucoup moins lourd de faire le calcul une fois à l'ajout de commentaire que de le refaire pour chaque post à chaque affichage de la page.
(je pense qu'un outter join aurait aussi pu résoudre ton premier problème sinon)

Pour ton autre problème, j'ai du mal à saisir ce que tu veux, mais j'ai l'impression que tu cherches à faire quelquechose comme du requêtage objet, c'est pas possible en base de données relationnelles. Par contre ne fait pas une requête par poste, ramène pour tous les posts et fait le tri à la sortie. C'est coûteux de balancer plein de requêtes. Surtout si t'es chez un hébergeur gratuit ou un mutualisé pas cher... Ils deviennent souvent susceptibles quand on tire inutilement sur la base.

 

[p4bl0]

Pour le contage des commentaires je te conseille fortement d'ajouter un champ dans ta table de posts que tu mets à jour chaque fois qu'un commentaire est ajouté ou supprimé (par exemple à l'aide d'un trigger). C'est beaucoup moins lourd de faire le calcul une fois à l'ajout de commentaire que de le refaire pour chaque post à chaque affichage de la page.
(je pense qu'un outter join aurait aussi pu résoudre ton premier problème sinon)

Pour ton autre problème, j'ai du mal à saisir ce que tu veux, mais j'ai l'impression que tu cherches à faire quelquechose comme du requêtage objet, c'est pas possible en base de données relationnelles. Par contre ne fait pas une requête par poste, ramène pour tous les posts et fait le tri à la sortie. C'est coûteux de balancer plein de requêtes. Surtout si t'es chez un hébergeur gratuit ou un mutualisé pas cher... Ils deviennent souvent susceptibles quand on tire inutilement sur la base.

C'est ce que je faisait jusqu'à présent de mettre un champs avec le compteur de commentaire, mais j'sais pas là j'avais pas envie

J'voulais avoir une bdd bien rangée avec seulement ce qui concerne le post dans la table posts par exemple. C'est vraiment coûteux les count() ? Si oui je rajoute ce champs ça coûte rien mais c'est moins joli ^^

J'ai pas pensé à essayer les inner et outer join, j'ai jamais utilisé et je connais pas ces trucs là faudra que je regarde la doc

Pour les tags, voilà le topo :
j'ai une table `tags` avec 3 champs : `id`, `name` et `counter` (pour le cloud ^^).
une table de jointure `_posts_tags` avec 2 champs : `pid` et `tid` (post id et tag id).

Donc quand j'affiche qu'un post, je récupère les tags comme ça :

SELECT `tags`.`name` FROM `tags` JOIN (`posts`, `_posts_tags`) ON (`posts`.`id`=`_posts_tags`.`pid` AND `tags`.`id`=`_posts_tags`.`tid`) WHERE `posts`.`id`=':postId';

Ça marche très bien et ce que je voulais savoir c'est si on peut faire quelque chose de similaire quand on récupère plusieurs posts (faire la même chose pour chaque post qu'on récupère).

C'est pour ça que je parlais de "retourner un tableau PHP", je voudrais récupérer un tableau qui contient les tags par post, en même temps que son titre etc...

Mais ça m'étonnerait que ce soit possible.


Est ce que c'est vraiment coûteux si c'est 5 requêtes ? (j'afficherais pas plus de 5 posts à la fois).

Sachant que je suis sur un petit serveur dédié (un kimsufi'08 à 20€/mo), mais qu'il va y avoir pas mal de site dessus (tous à petit traffic, je ne pense pas qu'un de ces sitesarrive à plus 1000 pages vu / jours avant très très longtemps voir jamais ! C'est des sites persos et des conneries de toutes façons ).

 

[p4bl0]

Ouais!! j'ai trouvé group_concat donc je peut récupérer tout les tags de tout les posts qui m'intéresse en une seule requêtes

Ça fait un truc comme ça :

SELECT group_concat(`tags`.`name` ORDER BY `tags`.`id` SEPARATOR ' ; ') AS `tags`
FROM `tags`
JOIN (`posts`, `_posts_tags`)
ON (`posts`.`id`=`_posts_tags`.`pid` AND `tags`.`id`=`_posts_tags`.`tid`)
WHERE `posts`.`status`>3
GROUP BY `_posts_tags`.`pid`
ORDER BY `posts`.`date` DESC
LIMIT 0,5;

héhé c'est génial MySQL je me doutais pas que c'était aussi puissant comme langage ! C'est impressionnant je comprend pourquoi les base de données c'est un métier à part entière !

Et du coup quand je récupère un poste seul, je fais tout en une seule requête :

SELECT `posts`.`id`, `posts`.`uid`, `posts`.`title`, `posts`.`body`,
       date(`posts`.`date`) AS `date`, time(`posts`.`date`) AS `time`,
       `sections`.`name` AS `section`,
       (SELECT group_concat(`tags`.`name` ORDER BY `tags`.`id` SEPARATOR ' ; ')
          FROM `tags`
          JOIN (`posts`, `_posts_tags`)
          ON (`posts`.`id`=`_posts_tags`.`pid` AND `tags`.`id`=`_posts_tags`.`tid`)
          WHERE `posts`.`id`='$this->id'
          GROUP BY `_posts_tags`.`pid`) AS `tags`,
        `nb_comments`
FROM `posts`
LEFT JOIN `sections`
ON `sections`.`id`=`posts`.`sid`
WHERE `posts`.`id`='$this->id'
LIMIT 1;

Ensuite j'ai plus qu'à faire un $post->tags = explode(' ; ', $post->tags); en PHP

ça déboîte !

 

[grumff]

Faire des tables bien rangées c'est très bien, c'est ce qu'on apprends partout, et ça s'appelle normaliser une base. Mais après, il y a ce qu'on appelle la dénormalisation, ça ne dispense pas de la première étape, mais ça consiste à faire des modifications pour optimiser les traitements, pour éviter de recalculer plein de fois des choses qui peuvent être mises à jour une seule fois, pour éviter de faire des jointures quand on peut s'en passer (très coûteux). Un count en temps que tel n'est pas coûteux, du moins s'il ne compte pas trop de choses et que le champ est indexé, sinon ça peut l'être si, mais multiplié par le nombre de posts, par le nombre de personnes qui vont afficher la page, ça ne sera pas négligeable. Après c'est qu'une question de volumétrie, tu n'auras sans doute pas de problème sur un serveur dédié, les problèmes arrivent plus vite chez les hébergeurs mutualisés où il n'y a qu'un seul serveur de base de données pour beaucoup d'utilisateurs, mais si un jour ton trafique s'accroît, ça pourra compter. C'est une habitude à prendre pour faire les choses correctement, rien de plus, rien de moins non plus.

Pour le group_contact, je n'ai jamais entendu parler de ça dans aucun SGBD, même pas sous Oracle, j'avoue que ça me surprend et je vais y jeter un oeil, merci d'avoir partagé l'info en tous cas.

 

[p4bl0]

Faire des tables bien rangées c'est très bien, c'est ce qu'on apprends partout, et ça s'appelle normaliser une base. Mais après, il y a ce qu'on appelle la dénormalisation, ça ne dispense pas de la première étape, mais ça consiste à faire des modifications pour optimiser les traitements, pour éviter de recalculer plein de fois des choses qui peuvent être mises à jour une seule fois, pour éviter de faire des jointures quand on peut s'en passer (très coûteux). Un count en temps que tel n'est pas coûteux, du moins s'il ne compte pas trop de choses et que le champ est indexé, sinon ça peut l'être si, mais multiplié par le nombre de posts, par le nombre de personnes qui vont afficher la page, ça ne sera pas négligeable. Après c'est qu'une question de volumétrie, tu n'auras sans doute pas de problème sur un serveur dédié, les problèmes arrivent plus vite chez les hébergeurs mutualisés où il n'y a qu'un seul serveur de base de données pour beaucoup d'utilisateurs, mais si un jour ton trafique s'accroît, ça pourra compter. C'est une habitude à prendre pour faire les choses correctement, rien de plus, rien de moins non plus.

Pour le group_contact, je n'ai jamais entendu parler de ça dans aucun SGBD, même pas sous Oracle, j'avoue que ça me surprend et je vais y jeter un oeil, merci d'avoir partagé l'info en tous cas.

Pour group_concat ça marche très bien en tout cas, et c'est bien pratique.

Pour les deux JOIN qu'il me reste, c'ets vraiment plus pratique comme ça. Parce que ça me permet de très facilement récupérer les tags d'un post, mais aussi les posts d'un tag, de même pour les sections .

 

[tatouille]

table posts

relational table post2comment
" for one given id post select from, it's faster and fully object"
id_post id_comment

table comments

 

[p4bl0]

table posts

relational table post2comment
" for one given id post select from, it's faster and fully object"
id_post id_comment

table comments

Toi tu dis qu'il vaut mieux faire comme pour les tags même pour les comments ?

Pourtant les comments sont rataché à un seul et unique post donc il me semblerais logique qu'il soit plus rapide de mettre un champ `pid` (post id) dans la table `comments` que de faire un JOIN non ?
(c'est ce que j'ai fais)

Pour les tags c'est pas pareil parce qu'un même tag peut être dans plein de posts.

Le seul "avantage" que je vois à ta méthode (mais c'en est pas vraiment un), c'est que je pourrait enlever le field `nb_comments` de la table `posts` et faire le count() sur `post2comments` ce qui serait peut-être plus rapide car la table et petite (je sais pas si ça marche comme ça, surtout que je faisais le count sur `comments`.`id` qui est indéxé, bon là aussi ça serait indéxé mais je sais pas si le nombre de champs compte pour le temps que met count()).

 

[tatouille]

peut importe les nombres de champs ou la rapidite
1- justement ce cas a besoin d'un pivot le sql s'en trouve optimise
2- dans ton model objet toutes les queries se retrouvent sur le meme model, ici je te laisse deviner
l'avenir de certains sqls
3- ca te permet d'envisager n'importe quelle manipe renverser/echanger
4- tu ne touche pas a l'integrite de ta table
5- tes posts peuvent etre lies a d'autres objets ansi que tes comments, ici je te laisse deviner
l'avenir de ta DB

perso, je travaille objet , donc avec des pointeurs

pour ta deuxieme question je te conseille de te faire un TP et implementer un algo type B-TREE (ce qu'utilise les DB)
tu touveras de facto la reponse

 

[p4bl0]

Tu as certainement raison, mais c'est pas un projet de dingue ni du code qui va être réutilisé par d'autre personne que moi pour des projets persos (si je le réutilise...) : c'est juste pour un petit blog!

C'est intéressant et je vais regarder tout ça, maislà je vais rester simple .


Par rapport à tes mails à propos de Belokan, pareil : c'est très intéressant et je les met de côté mais c'est bien trop complexe pource que je veux faire de Belokan à savoir un framework simple et léger dont le but et surtout d'avoir une organisation pour les fichiers de son projet et d'avoir quelques outils simples pour se facilité certaines tâches.

 

[molgow]

Pour ta requête, ne devrais-tu pas utiliser un "left join" ?

 

[tatouille]

Pour ta requête, ne devrais-tu pas utiliser un "left join" ?

oui si tu veux joindre sur les deux id a gauche, apres si les deux table on des zillions d'entrees ca risque d'etre rigolo mais pour ce cas c'est th best answer in the discussion

 

[p4bl0]

oui si tu veux joindre sur les deux id a gauche, apres si les deux table on des zillions d'entrees ca risque d'etre rigolo mais pour ce cas c'est th best answer in the discussion

Si une des tables en question dans cette discussion arrive à 500 entrées c'est déjà pas mal, et c'est loin d'arriver. Sur mon blog actuel j'ai à peine plus de 200 posts (depuis Novembre 2006...).

@moglow: tu veux dire dans la sous-requête ? À priori pas besoin vu que tout les posts auront au moins un tag. J'ai essayé avec et sans, les deux marche bien, avec ou sans tag

 

[tatouille]

Si une des tables en question dans cette discussion arrive à 500 entrées c'est déjà pas mal, et c'est loin d'arriver. Sur mon blog actuel j'ai à peine plus de 200 posts (depuis Novembre 2006...).

@moglow: tu veux dire dans la sous-requête ? À priori pas besoin vu que tout les posts auront au moins un tag. J'ai essayé avec et sans, les deux marche bien, avec ou sans tag

run explain

 

[p4bl0]

run explain

Tout est pareil sauf qu'il y a un "using where" en plus dans les "extra" de la subquery pour la table `_posts_tags` (la table de jointure) quand je met les LEFT JOIN à la place des JOIN.

Donc à priori ça serait même moins performant vu qu'il y a un truc de fait en plus ? Ou alors c'est mieux qu'il utilise d'abord le where ? (mais pourquoi ?)

 

[grumff]

Tout est pareil sauf qu'il y a un "using where" en plus dans les "extra" de la subquery pour la table `_posts_tags` (la table de jointure) quand je met les LEFT JOIN à la place des JOIN.

Donc à priori ça serait même moins performant vu qu'il y a un truc de fait en plus ? Ou alors c'est mieux qu'il utilise d'abord le where ? (mais pourquoi ?)

Faudrait le plan d'exécution complet pour voir ce que ça change, mais des choses en plus ça ne veut pas forcément dire des performances en moins.
Il y a un certain nombre d'instructions qui ne coûtent rien ou presque, ou qui vont filtrer des données pour travailler sur des jeux plus réduits après et gagner en performances. Tant que le nombre de jointure est restreint et que les accès se font par des indexs, ça va plutôt vite, mais un seul accès en fullscan peut plomber complètement les perfs. Bref, c'est pas la quantité de choses dans le plan d'exécution qui importe, mais la qualité.

 

[p4bl0]

Faudrait le plan d'exécution complet pour voir ce que ça change, mais des choses en plus ça ne veut pas forcément dire des performances en moins.
Il y a un certain nombre d'instructions qui ne coûtent rien ou presque, ou qui vont filtrer des données pour travailler sur des jeux plus réduits après et gagner en performances. Tant que le nombre de jointure est restreint et que les accès se font par des indexs, ça va plutôt vite, mais un seul accès en fullscan peut plomber complètement les perfs. Bref, c'est pas la quantité de choses dans le plan d'exécution qui importe, mais la qualité.

Je m'en doute bien (edit: 2ème partie de ta première phrase), mais le where se fait sur toute la table, mais sur un champ indexé (et numérique en plus, ça joue peut-être), c'est pour ça que je posais la question .

Donc vu que c'est indexé, vaut mieux faire ce where alors, donc je remet les LEFT JOIN. Merci.

le plan d'execution complet (si c'est bien les infos données par le explain), quel qu'il soit est identique pour les deux requêtes à part le point que j'ai soulevé (le where sur la table de jointure qui ne comprends que deux champs INT et indéxé), donc vu que ce qui nous intéresse c'est laquelle de ces deux requêtes est la plus rapide, le plan d'execution complet n'est pas si important amha.


Merci en tout cas, j'ai plus de questions pour le moment

EDIT : Mais si ça t'intéresse je vais refaire le explain... baah :mouais: maintenant c'est exactement la même chose... -_-' Qui a touché à mon serveur MySQL cette nuit ? ^^
Bon... ben pour les deux ça donne ça (c'est sur la 3ème ligne dernier champ qu'il y avait le "Using where ;" en moins hier quand j'enlevais les LEFT.