Sortieren nach Bewertungen in einer Datenbank - Wohin mit diesem SQL? (PHP/MySQL)

Question

OK - ich komme gleich zur Sache - hier ist der fragliche PHP-Code:

<h2>Highest Rated:</h2>

    <?php 

        // Our query base               
        $query = $this->db->query("SELECT * FROM code ORDER BY rating DESC");

        foreach($query->result() as $row) {
    ?>  
        <h3><?php echo $row->title." ID: ";echo $row->id; ?></h3>
            <p class="author"><?php  $query2 = $this->db->query("SELECT email FROM users WHERE id = ".$row->author);
echo $query2->row('email');?></p>
            <?php   echo ($this->bbcode->Parse($row->code)); ?>

        <?php } ?>

Tut mir leid, dass es ein bisschen unordentlich ist, es ist noch ein Entwurf. Jedenfalls habe ich recherchiert, wie ich ein Bewertungssystem verwenden kann - vorher hatte ich ein einziges 'Bewertungs'-Feld, wie Sie es durch SELECT * FROM code ORDER BY rating DESC sehen können. Mir wurde jedoch schnell klar, dass die Berechnung von Durchschnittswerten so nicht machbar war, also habe ich fünf neue Spalten erstellt - rating1, rating2, rating3, rating4, rating5. Wenn also 5 Benutzer etwas mit 4 Sternen bewerten, steht in rating4 5... macht das Sinn? Jede ratingx-Spalte zählt, wie oft die Bewertung abgegeben wurde.

Also, wie auch immer: Ich habe diese SQL-Anweisung:

SELECT id, (ifnull(rating1,0) + ifnull(rating2,0) + ifnull(rating3,0) + ifnull(rating4,0) + ifnull(rating5,0)) / ((rating1 IS NOT NULL) + (rating2 IS NOT NULL) + (rating3 IS NOT NULL) + (rating4 IS NOT NULL) + (rating5 IS NOT NULL)) AS average FROM code

Wieder chaotisch, aber hey. Jetzt muss ich wissen, wie ich diese SQL-Anweisung in mein Skript einbauen kann. Idealerweise würde die Gesamtabfrage lauten: "SELECT * FROM code ORDER BY ( die wirklich lange Anfrage, die ich gerade gestellt habe ) DESC', aber ich kann mir nicht vorstellen, dass das funktioniert... wie mache ich das? Abfrage, das Ergebnis in einer Variablen speichern, oder so ähnlich?

Wenn das keinen Sinn ergibt, tut es mir leid! Aber ich weiß die Hilfe wirklich zu schätzen :)

Jack

Answer 1

Sie sollten noch einmal ganz von vorne anfangen.

<?php
$query = $this->db->query("SELECT * FROM code ORDER BY rating DESC");
foreach($query->result() as $row) {
    $this->db->query("SELECT email FROM users WHERE id = ".$row->author;
}

Jederzeit Sie dies in Ihrem Code sehen, stoppen Sie sofort Ihre Arbeit. Dafür sind JOINs da. Sie wollen fast nie eine Schleife über die Ergebnisse einer Abfrage ziehen und mehrere Abfragen innerhalb dieser Schleife durchführen.

SELECT code.*, users.email 
FROM code
JOIN users ON users.id = code.author
ORDER BY rating DESC

Diese Abfrage fasst alle Daten in einem einzigen Ergebnissatz zusammen, so dass das Problem der N+1-Abfrage entfällt.

Ich gehe auf den Rest Ihrer Frage erst ein, wenn Sie Ihre Frage etwas präzisieren und klarstellen, was Sie zu tun versuchen.

Answer 2

Wenn Sie Ihre Tabellen wieder ändern möchten, hier mein Vorschlag:

warum speichern Sie nicht zwei Spalten: RatingTotal und RatingCount. Jeder Benutzer, der eine Bewertung abgibt, erhöht RatingCount um eins, und die jeweilige Bewertung (5,4,4.2 usw.) wird zu RatingTotal addiert. Sie könnten dann einfach ORDER BY RatingTotal/RatingCount

Außerdem hoffe ich, dass Sie speichern, welche Nutzer die einzelnen Artikel bewertet haben, damit sie nicht mehrfach abstimmen und den Durchschnitt in ihre Richtung lenken.

Answer 3

Zunächst würde ich entscheiden, ob Ihre Anwendung eher schreib- oder leselastig ist. Wenn es viel mehr Lese- als Schreibvorgänge gibt, dann sollten Sie den Arbeitsaufwand für Lesevorgänge minimieren (wie zum Beispiel bei diesem Skript). Unter der Annahme, dass es sich um eine leseintensive Anwendung handelt, was bei den meisten Webanwendungen der Fall ist, würde ich vorschlagen, den kombinierten Durchschnitt in einer separaten Spalte zu speichern und ihn jedes Mal neu zu berechnen, wenn ein Benutzer eine neue Bewertung hinzufügt.

Andere Optionen sind:

• Versuchen Sie, nach dem Namen der berechneten Spalte "Durchschnitt" zu bestellen. SQL Server unterstützt dies. . nicht sicher über mysql.
• Verwenden Sie eine Ansicht. Sie können eine Ansicht auf Ihre Basistabelle erstellen, die die Durchschnittsberechnung für Sie durchführt, und Sie können diese abfragen.

Auch, unabhängig von Ihrer Frage, tun Sie nicht eine separate Abfrage für jeden Benutzer in Ihrer Schleife. Verbinden Sie die Tabelle users mit der Tabelle code in der ursprünglichen Abfrage.

Answer 4

Sie sollten sie in den SELECT-Teil aufnehmen:

SELECT *, (if ....) AS average FROM ... ORDER BY average

編集する。 unter der Annahme, dass Ihre ifnull-Anweisung tatsächlich funktioniert...

Vielleicht möchten Sie auch Joins in Betracht ziehen, um zu vermeiden, dass die Datenbank für jeden Benutzer erneut abgefragt wird; Sie können alles in einer Select-Anweisung erledigen.

Abgesehen davon würde ich auch sagen, dass Sie nur einen Durchschnitt und die Anzahl der Gesamtstimmen benötigen, das sollte Ihnen alle Informationen geben, die Sie brauchen.

Answer 5

Einige ausgezeichnete Ideen, aber ich denke, der beste Weg (wie sidereal sagte, dass es mehr lesen schwer, dass schwer zu schreiben) wäre, Spalten rating und times_rated haben, und tun Sie einfach etwas wie dieses:

new_rating = ((times_rated * rating) + current_rating) / (times_rated + 1)

current_rating ist die Bewertung, die angewendet wird, wenn die Person auf die kleinen Sterne klickt. Dadurch wird die Bewertung des aktuellen Benutzers in einem Durchschnitt mit der aktuellen Bewertung gewichtet.