Lister les catégories avec les derniers commentaires/activités avec MYSQL

Question

Je développe un site où les utilisateurs peuvent poster des commentaires et où chaque commentaire est classé par catégorie. J'ai une page où les utilisateurs peuvent aller et voir une liste de toutes les catégories sur le site avec les 5 derniers commentaires postés dans ces catégories.

Les informations que je dois extraire de la base de données sont les suivantes :

• Une liste de catégories
  • 5 commentaires dans chaque catégorie

Voici ce que j'ai en ce moment (simplifié en PHP de base) :

echo "<ul>";
$query = mysql_query("SELECT * FROM categories");
while($result = mysql_fetch_assoc($query)){

    echo "<li><h2>{$result['category_name']}</h2>";

    $query_comments = mysql_query(
                                 "SELECT * FROM comments WHERE ".
                                 "category_id = '{$result['id']}' ".
                                 "ORDER BY created_at DESC LIMIT 5");
    while($result_comments = mysql_fetch_assoc($query_comments)){
        echo "{$result_comments['username']} wrote {$result_comments['text']} on {$result_comments['created_at']}<br>";
    }

    echo "</li>";

}
echo "</ul>";

Cela ressemblerait à ceci (en supposant que mes catégories sont des noms de fruits)

Apple
    Jay wrote blah blah blah - August 5, 2009
    Bob wrote hello hello hello - August 5, 2009
    Tom wrote super super - August 5, 2009
    Edward wrote no no no - August 5, 2009
    Kaysie wrote super no! - August 5, 2009

Orange
    Cassie wrote ye ye ye ye - August 5, 2009
    Alfonce wrote whoohoo - August 5, 2009
    Arthur wrote love oranges - August 5, 2009
    Alice wrote yes yes yes - August 5, 2009
    Xavier wrote Lorem ipsum dolor sit amet - August 5, 2009

Strawberry
    Chris wrote Lorem ipsum dolor sit amet - August 5, 2009
    Hubert wrote Lorem ipsum dolor sit amet - August 5, 2009
    Martin wrote Lorem ipsum dolor sit amet - August 5, 2009
    Lyon wrote Lorem ipsum dolor sit amet - August 5, 2009
    Paris wrote Lorem ipsum dolor sit amet - August 5, 2009

Blueberry
    etc...

Le problème est que, s'il y a beaucoup de catégories, j'aurai des problèmes de performance, surtout si beaucoup d'utilisateurs se servent du site.

Je m'efforce de trouver un moyen de réduire le nombre de requêtes nécessaires. Quelqu'un a-t-il une idée de la façon dont je pourrais le faire ?

UPDATE J'ai essayé de faire un LEFT JOIN de la table des catégories avec la table des commentaires mais je n'ai pas trouvé le moyen de limiter le nombre de commentaires par catégorie car si j'utilise LIMIT 5, cela ne limite que le nombre de commentaires retournés.

Answer 1

J'ai parlé à un spécialiste des bases de données et il s'avère que c'est un peu difficile avec MySQL.

Quelque chose comme cela fonctionnerait bien dans PostgreSQL :

SELECT * FROM categories
LEFT JOIN comments ON categories.id = comments.category_id
WHERE comments.id IS NULL OR
comments.id IN ( SELECT id FROM comments AS a2 WHERE categories.id = a2.category_id ORDER BY id DESC LIMIT 5 )

Malheureusement, MySQL ne supporte pas le LIMIT dans les sous-requêtes. Il s'est gratté la tête et a dit qu'il existait une solution de contournement, mais elle n'avait pas l'air très jolie. À ce stade, je me suis dit qu'il valait mieux utiliser plusieurs requêtes. S'il s'agit d'un problème de performances, il peut s'agir de données que vous mettez temporairement en cache.

Désolé, je ne suis pas d'une grande aide :)

Vieille mauvaise réponse : Essayez d'utiliser un LEFT JOIN dans votre requête, avec les catégories à gauche (ainsi toutes les catégories sont toujours retournées, qu'elles aient ou non des commentaires) et la table des commentaires à droite. Cela réduira le nombre de requêtes à une seule.

Answer 2

Tu pourrais utiliser un joint :

SELECT categories.category_name, comments.*
FROM comments 
LEFT JOIN categories ON categories.category_id=comments.category_id

et changer la façon dont vous itérez à travers les résultats.

Answer 3

Quelque chose comme ça, peut-être ?

SELECT cat.*, com.*
FROM categories cat, comments com
WHERE com.categoryid=cat.id
ORDER BY cat.category_name ASC, com.created_at DESC

Note : Il serait plus sage d'étendre les * pour ne sélectionner que ce dont vous avez besoin (et aussi éliminer toute possibilité d'ambiguïté).

EDIT : après avoir lu plus attentivement, je ne pense pas que vous vouliez cette réponse ni aucune des autres réponses, puisque cela va récupérer tous les commentaires de la base de données, pas seulement les plus récents.

La meilleure solution à laquelle je peux penser pour l'instant est que vous choisissiez combien de temps avant est ignorable, et ajoutez com.created_at > [date] pour limiter au nombre total de commentaires sélectionnés. Cela peut signifier que certaines catégories n'affichent pas 5 commentaires même si l'utilisateur en a fait 5 ou plus, mais il y a longtemps.

Answer 4

Je ne peux pas voir tout votre code, mais je pense vraiment que vous devriez faire une fonction

get_categories()
{
    //PSEUDO: $results_array;
    //PSEUDO: return $results_array = mysql_results;
}

Si vous avez le temps, je vous suggère de migrer vers AOP

votre HTML serait alors :

// rough PSEUDO Code
<ui>
    <li>
         <?php 
             foreach ($results_array as $key => $value)
             {
                 echo(htmlentitites($value);
             }
         ?>

    </li>

</ui>

De cette façon, vous déplacez votre code vers une structure plus organisée.