CODEWITHSUNDEEP
phpsqlrating
qkp
qkp

Reputation: 161

How to implement a rating system that also supports querying for top rated "today, week, month"

I've been looking for some good rating system tutorials on the internet and I can't seem to find the one i'm looking for! I've come across this article http://www.expertcore.org/viewtopic.php?f=74&t=1451 which uses the Bayesian Rating algorithm and a like/dislike rating and that one is the rating system I want except that system can only make a "Top rated list of all time", I want to be able to make a "Top rated of today", "Top rated of this week" and "Top rated of this month" aswell. I'm not to sure of how I'll have to approach this problem because I'm not really great at database designs and I don't want to encounter performance issues later on!

Example I have these three tables.

Table "Item" id name

Table "User" id name

Table "Vote" id itemID userID date

The normal algorithm is this: br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / (avg_num_votes + this_num_votes)

So to get a top rated list of all time I'll have to calculate the BR for every item I have. Would it mean if I want the top rated list of this month I just do the same but check if the date of the votes are valid? Please give me tips and hints !

Upvotes: 0

Views: 1051

Answers (1)

Steve
Steve

Reputation: 1636

Your table looks like it would work, you would just need to select all Vote records that happen in a date range. So, for "this week", for all the values in that equation, you could apply a date filter when selecting to only choose rows in this week.

For: avg_num_votes, avg_rating, this_num_votes, this_rating; all would need to be created off of records from "this week" otherwise your weights will be way off, and your "top this week" would only show records from all time.

Same with "top rated this month" and "top rated today" you just want to see votes that have been voted in that time frame, and not take into account all votes from all time.

For speed, you would want to filter as much as possible at the database level, so that would probably mean joining the Item and Vote tables, then selecting only where the date of the Vote is in the range you want, then working with that data.

His own code would demonstrate this well, just adding in a filter to his select statements:

$result = mysql_query(”SELECT AVG(item),AVG(vote) FROM itemvotes WHERE vote>’0’ GROUP BY item AND date BETWEEN '2010-08-20' AND '2010-08-21'”) or die(mysql_error());

// this item votes and ratings
$result = mysql_query(”SELECT COUNT(item),AVG(vote) FROM itemvotes WHERE item=’$theItem’ AND vote>’0’ AND date BETWEEN '2010-08-20' AND '2010-08-21'”) or die(mysql_error());

This might not be exactly the same (or work at all, that example was a little funky), but it demonstrates what I mean.

Upvotes: 2

Related Questions