Page 1 of 1

Rating and Ranking System

Posted: Tue Mar 16, 2010 8:36 am
by Harvester65
We are trying to come up with a ranking system that solves several problems.
Let's say I have a site where people can upload photos. I want visitors to the site to be able to give those photos a rating and then have the top rated photos ranked from say 1 to 100.
The dilemma is , I don't want any 2 photos to share the same rank. For example I don't want there to be more than one photo in the number 1 ranking spot.
Let's say these are all the variables associated with each photo:
rating
# of views
# of ratings
# of comments
age of the photo on the site

Dilema number 2) I don't want the oldest photos on the site to have an unfair advantage against newer photos. In other words, if I'm a photographer and I want to put my photo on the site to try to get the number 1 spot, and I see that "weight" is given to # of views and # of ratings, I dont want to be dismayed because I think I can never get the top spot because my photo is brand new and it will take forever to accumulate a lot of views and ratings.

I'm trying to figure out if there is some way where brand new photos can have equal opportunity to get into the number one spot as the oldest photos on the site. At the same time, if the #1 is rated a 9.5 , I don't want a brand new photo to get just one 10 vote and take over the top spot with only a single rating.
I don't want to put in some variable that randomly sorts this out where number 1 is number one due to a random variable. I want the # 1 to be there because the community rated it there.

I'm really not looking so much for code, rather a top level strategy.

I'm open to any ideas. Thanks.

Re: Rating and Ranking System

Posted: Tue Mar 16, 2010 11:12 am
by jraede
Well, in your SQL query (assuming you're using that to store the data) you can order the results by views, then ratings, and then # of comments. So if two photos have the same # of views, then the one with the highest rating would appear first, and if they have the same rating, the one with the most comments would appear first. Then, in the loop you're using to display the results, you could just echo $rank, or whatever variable you want to use, and then add 1 to it. Does that make sense?

Re: Rating and Ranking System

Posted: Tue Mar 16, 2010 1:24 pm
by Harvester65
jraede wrote:Then, in the loop you're using to display the results, you could just echo $rank, or whatever variable you want to use, and then add 1 to it. Does that make sense?
Yes i think so, thanks.

Re: Rating and Ranking System

Posted: Tue Mar 16, 2010 3:30 pm
by John Cartwright
Perhaps something like this (assuming you have a normalized database structure).

Code: Select all

SELECT AVG(votes.rating) AS avgrating, photos.* FROM photos
LEFT JOIN votes ON votes.photo_id = photos.id
WHERE DATE_ADD(votes.created, INTERVAL 1 MONTH) > NOW()
GROUP BY photos.id
ORDER BY avgrating DESC, photos.views DESC
In a nutshell, will average the ratings of all the votes for each photo, but only include votes a month old. It will order them to include the highest rated first, and based on an average tie, it will give priority to photos with higher views.

Re: Rating and Ranking System

Posted: Tue Mar 16, 2010 5:22 pm
by Eran
John's suggestion is probably the way to go. Just one minor caveat, I'd use:
[sql]WHERE votes.created > NOW() - INTERVAL 1 MONTH[/sql]
instead of
[sql]WHERE DATE_ADD(votes.created, INTERVAL 1 MONTH)  > NOW()[/sql]
So it doesn't have to be calculated for each row and can use an index.

Re: Rating and Ranking System

Posted: Tue Mar 16, 2010 10:09 pm
by John Cartwright
pytrin wrote:John's suggestion is probably the way to go. Just one minor caveat, I'd use:
[sql]WHERE votes.created > NOW() - INTERVAL 1 MONTH[/sql]
instead of
[sql]WHERE DATE_ADD(votes.created, INTERVAL 1 MONTH)  > NOW()[/sql]
So it doesn't have to be calculated for each row and can use an index.
Good tip, thanks :)