// $ts = time in seconds between an entry and a given date (lets say January 19th)
// $z = number of "votes" an entry has
function rank($ts,$z) {
$rating = log10($z) + (-1*$ts)/45000;
return $rating;
}
SELECT LOG10(`votes`)+(-1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) FROM post ORDER BY `id` DESC;
But I can't figure out how to return the results sorted by the rank that's given. I basically need to display a list of ids ordered by the calculated ranking (descending).
wrap the expression in quotes and add an alias -
[sql]SELECT ( LOG10(`votes`)+(-1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) ) AS `rank` FROM post ORDER BY `rank` DESC;[/sql]
pytrin wrote:wrap the expression in quotes and add an alias -
Thank you very very much.
So now I'm able to return the results along with the id using...
[sql]SELECT ( LOG10(`votes`)+(1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) ) AS `rank`, `id` FROM polls ORDER BY `rank` DESC;[/sql]
Did my query translate well from the php version? Is this a valid way of going about ranking entries like this? Or is there a better way using separate tables?
Yes, it's a perfectly valid way to rank entries. The only "problem" with it is that you can't use an index for sorting, which will have some performance implications when the table gets larger. At that point you could store the results of ranking in a dedicated field in the table (named 'rank' or something similar), index it and sort by it
pytrin wrote:Yes, it's a perfectly valid way to rank entries. The only "problem" with it is that you can't use an index for sorting, which will have some performance implications when the table gets larger. At that point you could store the results of ranking in a dedicated field in the table (named 'rank' or something similar), index it and sort by it
Ok cool, so indexing and storing the 'rank' in each row would be beneficial, even if the rank is changing each time the entry receives a new 'vote'?
That's the problem with storing the rank calculation - it stops being really 'real-time'. You need to run scheduled updates (cron jobs or task scheduler) in whatever intervals is acceptable to your application. But I'll repeat, only start going this way when you feel a need performance-wise. Your previous solution will work very well for a small tables.
pytrin wrote:That's the problem with storing the rank calculation - it stops being really 'real-time'. You need to run scheduled updates (cron jobs or task scheduler) in whatever intervals is acceptable to your application. But I'll repeat, only start going this way when you feel a need performance-wise. Your previous solution will work very well for a small tables.
Awesome, thanks. Last question. I haven't yet worked with trying to fit a number like -525.156593861166 into a column (an example of a ranking returned). What field type should I be using for numbers like this?
You should use the FLOAT type if small rounding errors are not a concern for you - which I would think would be the case here. Use DECIMAL if you need exact representation of the number (mostly used for fiscal numbers). http://dev.mysql.com/doc/refman/5.0/en/ ... types.html