CODEWITHSUNDEEP
phpsqlaverage
Mus Harlem
Mus Harlem

Reputation: 175

Best average rating

I have created a simple rating system for news articles. The news articles are stored in the database table called 'articles'. Each article has a unique id, starting from 1.

So I have 2 articles, ID 1 and ID 2.

I also have a table called 'ratings' that takes the users unique ID, the article ID and the rating that the user gave.

If I give an article with ID 2 a 5/5 star rating, it goes into the 'ratings' table, with article ID 2, my user ID and the rating of 5.

I have figured out how to display the average rating of each article, but I would like to find out how to show the BEST average rating of articles in descending order. Is that at all possible? How could this be done?

Here is how I find the average:

<?
    $votesForThis = 0;
    $sql = "SELECT * FROM ratings WHERE articleID = ".$articleID." ORDER BY id ASC";
    // Check if there are results
    if ($result = mysqli_query($con, $sql)) {
        // Loop through each row in the result set
        while($row = mysqli_fetch_assoc($result)) {
            $votesForThis++;
        }
    }


    $result = mysqli_query($con, 'SELECT SUM(vote) AS vote_sum FROM ratings WHERE articleID=' . $articleID);
    $row = mysqli_fetch_assoc($result); 
    $voteSum = $row['vote_sum'];

    $averageVotes = $voteSum / $votesForThis;
?>

Upvotes: 0

Views: 142

Answers (4)

jeprubio
jeprubio

Reputation: 18012

I would use de-normalization in this one. I would use triggers to update a previously created column on table articles which would store it's average rating.

I would have posted an example of trigger but you haven't posted which database are you using.

Mysql: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

Postgresql: https://www.postgresql.org/docs/9.1/sql-createtrigger.html

Each time a rating it's done, updated or deleted a trigger would update this column with it's current average using the built-in avg function.

At the end you'll only have to create a select on the articles table ordered by this rating column desc.

And create an index on this average rating column to have even faster results.

Upvotes: 1

Malakiya sanjay
Malakiya sanjay

Reputation: 208

SELECT   articleID, AVG(vote)
FROM     ratings

GROUP BY articleID,vote

ORDER BY  DESC

used this Query

Upvotes: 0

Farzad Rastgar Sani
Farzad Rastgar Sani

Reputation: 371

The best practice for doing this is to add a new column to your article table called average_rating and update it with a cron job or after every voting.

Keep in mind that after a while your rating table will become giant and calculating average rating on every page refresh will put huge load on your server.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311823

MySQL has an avg function you can use instead of implementing this logic yourself. From there, it's just a matter of grouping by the article ID and ordering by the average:

SELECT   articleID, AVG(vote)
FROM     ratings
GROUP BY articleID
ORDER BY 2 DESC

Upvotes: 3

Related Questions