Jump to content

Ranking Algorithm Into Query


katlis

Recommended Posts

So I have this reddit-esque ranking function...

 

function rank($ts,$z) {

$rating = log10($z) + (-1*$ts)/45000;
return $rating;

}

 

$z = number of votes

$ts = number of seconds between January 19th and the timestamp the entry was created... so I guess something like TIMESTAMPDIFF(SECOND,'2010-01-19 01:00:00',`post.published`)

 

I'm pretty lost on how to convert this into a query... I'd greatly appreciate any help anyone can provide on this.

Thanks! I'm using 5.1.43.

Link to comment
https://forums.phpfreaks.com/topic/193131-ranking-algorithm-into-query/
Share on other sites

I think I'm getting there....

SELECT LOG10(`votes`)+(-1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) FROM post ORDER BY `id` ASC;

 

Atleast I'm getting some sort of data back. Any one have input on how I can clean this up... and at the same time, return a list of the `id`'s ordered by the rating this formula is returning?

Thank you.

 

EDIT: A little more background on this... I have a list of articles that have votes in my "post" table. The important columns are `published` (the timestamp of publishing), `votes`, and `id`. I want to use the formula to sort through these entries and return the list of IDs ordered by rating of this "algorithm" (in desc).

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.