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.
Rating and Ranking System
Moderator: General Moderators
Re: Rating and Ranking System
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?
-
Harvester65
- Forum Newbie
- Posts: 2
- Joined: Tue Mar 16, 2010 7:52 am
Re: Rating and Ranking System
Yes i think so, thanks.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?
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Rating and Ranking System
Perhaps something like this (assuming you have a normalized database structure).
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.
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 DESCRe: Rating and Ranking System
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.
[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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Rating and Ranking System
Good tip, thankspytrin 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.