Requête mysql : Tri complexe des commentaires par votes

tagada · 13 Octobre 2010

Bonjour, tout est dans le titre !

Pour les commentaires des articles, j'ai une table 'commentaires' avec notamment les champs id, date et commentaire :

Code:

------------------------------------------
ID  |   DATE    |   COMMENTAIRE         |
------------------------------------------
1   |   12/10   |  comm 1               |
------------------------------------------
2   |   12/10   |  comm 2               |
------------------------------------------
3   |   13/10   |  comm 3               |
------------------------------------------

J'ai une autre table 'votes' avec les champs id, comment (= id table commentaires) et vote
Le champ vote=1 est un vote pour, vote=0 est un vote contre.

Code:

------------------------------------
ID   |   COMMENT   |       VOTE     |
------------------------------------
1   |       1      |      1         |
------------------------------------
2   |       2      |      0         |
------------------------------------
3   |       1      |      1         |
------------------------------------

Dans cet exemple le commentaire 1 a 2 votes pour, le comm 2 a 1 vote contre et le com 3 n'a pas de vote.

Avec la requête suivante, j'obtiens la liste des commentaires par date inverse :

Code:

SELECT * FROM commentaires WHERE (je vérifie que le com. corresponde à l'article) ORDER BY date DESC

Le problème c'est que les votes s'affichent aléatoirement. Je souhaiterait que les commentaires les mieux notés s'affichent en haut, les plus mal notés en bas, et les sans notes au milieu, donc d'après la table ci-dessus, le commentaire 1 puis le comm 3 et enfin le com 2.

La requête suivante me donne les commentaires les mieux notés, les plus mal notés et enfin le reste :

Code:

SELECT * FROM commentaires AS c LEFT JOIN votes AS v ON v.comment=c.id WHERE (je vérifie que le com. corresponde à l'article) ORDER BY v.vote DESC, date DESC

Dans l'idéal je souhaiterais que :

- les commentaires les mieux notés (si la somme de ses votes pour est supérieure à celle des contres) apparaissent en haut puis régressivement
- les commentaires sans note (donc sans entrée dans la table vote), au milieu
- les commentaires les plus mal notés (si la somme de ses votes contre est supérieure à celle des pour) apparaissent en bas
- cerise sur le gâteau, les derniers commentaires des dernières x heures apparaissent en haut (peut importe leur vote) puis cèdent leur place. Mais je me contenterais des 3 premiers points.

Et c'est la que je patauge. J'ai fait des essais avec des group by, des cases, des sum, sans aucun résultat.

Je me doute qu'il faut additionner tous les commentaires de la table 'commentaires' (un SUM(*) sur cette table?), puis additionner tous ceux dont la valeur est 1, puis tout ceux dont la valeur est 0, puis en déduire ceux qui n'ont aucune valeur et enfin les trier.

Mais je n'ai aucune idée de la façon de le faire. Une piste please?
Merci

spout · 13 Octobre 2010

Il y a une méthode pour le tri des votes pondéré d'après le nombre total de votes:
http://en.wikipedia.org/wiki/Bayesian_average

Exemple en PHP/MySQL:
http://www.kobrascorner.com/tech/weighted-bayesian.php

tagada · 13 Octobre 2010

spout a dit:
Il y a une méthode pour le tri des votes pondéré d'après le nombre total de votes:
http://en.wikipedia.org/wiki/Bayesian_average

Exemple en PHP/MySQL:
http://www.kobrascorner.com/tech/weighted-bayesian.php

Wouah! quelle rapidité! merci je regarde ça !

EDIT : Mouais, je me disais... :?

spout · 13 Octobre 2010

tagada a dit:
EDIT : Mouais, je me disais... :?

:?:

Exemple plus simple:

Code:

$avg_num_votes = 18; // Average number of votes in all products
$avg_rating = 3.7; // Average rating for all products
$this_num_votes = 6; // Number of votes for this product
$this_rating = 4; // Rating for this product


$bayesian_rating = ( ($avg_num_votes * $avg_rating) + ($this_num_votes * this_rating) ) / ($avg_num_votes + $this_num_votes);

Source: http://snipplr.com/view/29775/bayesian-rating--weighted-voted-rating-system/

Aaarrrgggh · 13 Octobre 2010

Hello,

est-ce vraiment pertinent ce genre de tri ? Les utilisateurs ne vont rien comprendre, surtout si certains répondent à d'autres, non ?

spout · 13 Octobre 2010

Aaarrrgggh a dit:
Hello,

est-ce vraiment pertinent ce genre de tri ? Les utilisateurs ne vont rien comprendre, surtout si certains répondent à d'autres, non ?

+1 j'ai pensé pareil, pour trier des commentaires ça ne va pas être génial

tagada · 13 Octobre 2010

spout a dit:

:?:

Exemple plus simple:

Code:

$avg_num_votes = 18; // Average number of votes in all products
$avg_rating = 3.7; // Average rating for all products
$this_num_votes = 6; // Number of votes for this product
$this_rating = 4; // Rating for this product


$bayesian_rating = ( ($avg_num_votes * $avg_rating) + ($this_num_votes * this_rating) ) / ($avg_num_votes + $this_num_votes);

Source: http://snipplr.com/view/29775/bayesian-rating--weighted-voted-rating-system/

Oui, je comprends bien (enfin, j'essaie), mais ma logique de tri est plus simple : il s'agit de simples additions.

Le commentaire qui aura 357 votes pour et 327 contre sera tout en haut tandis que celui qui aura 356 pour et 0 contre sera 2ème, celui qui aura 200 pour et 300 contre dernier, et au milieu les commentaires sans vote.

Donc pas de moyenne pour le classement...

tagada · 13 Octobre 2010

Aaarrrgggh a dit:
Hello,

est-ce vraiment pertinent ce genre de tri ? Les utilisateurs ne vont rien comprendre, surtout si certains répondent à d'autres, non ?

En fait il s'agit surtout d'un tri pour éviter la modération (d'ou les commentaires des dernières heures en haut si c'est possible), ce n'est pas comme un forum avec une arborescence (pas de réponses).

Mais je comprends que ça semble bizarre