Position ranking, order of trust using Reddit ranking algorithms

Question

I am interested in using this rating class based on an article by Evan Miller to rank the table I have that has upvotes and downvotes. I have a system that is very similar to the up / down polling system for the event site I'm working on, and using this ranking class, I feel the results will be more accurate. My question is, how can I arrange the hotness function?

private function _hotness($upvotes = 0, $downvotes = 0, $posted = 0) { $s = $this->_score($upvotes, $downvotes); $order = log(max(abs($s), 1), 10); if($s > 0) { $sign = 1; } elseif($s < 0) { $sign = -1; } else { $sign = 0; } $seconds = $posted - 1134028003; return round($order + (($sign * $seconds)/45000), 7); } 

I believe that every time a user votes, I may have a column in my table that contains the hotness data recounted for the new vote and the order for that column on the main page. But I'm interested in doing this more on the fly by turning on the function above, and I'm not sure if this is possible.

From Evan Miller, he uses:

 SELECT widget_id, ((positive + 1.9208) / (positive + negative) - 1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / (positive + negative)) / (1 + 3.8416 / (positive + negative)) AS ci_lower_bound FROM widgets WHERE positive + negative > 0 ORDER BY ci_lower_bound DESC; 

But I rather do not do this calculation in sql, as I feel it is random and difficult to change line by line if I use this code on multiple .etc pages.

Answer 1

You are right, the request is also quite dirty and expensive.

Mixed PHP / MySQL on the fly is a bad idea, since you will need to select values ​​for all messages and calculate the heat, and then select the list of the hottest. Extremely expensive.

You should consider storing at least part of your calculation in a database. A certain order should go to the database. It is always better to calculate something and save only once every time you save / update, and not calculate every time it is displayed. Try to make a mark about how much time you save by calculating the order when saving / updating, and not every time you calculate the ardor. It’s good that the order never changes, unless someone increases / decreases, which you save in dB in any case, the same for the sign.

Even if you save the character in db, you cannot avoid the on-the-fly calculation due to the published timestamp parameter.

I would see what a difference it is and where it makes a difference and calculates a hotness with a CLI script every x time only for those scenarios where it is crucial, every time it makes less difference.

Taking this approach, you will recount the temperature only when necessary. This will make your application more efficient.

Answer 2

Access to the appropriate “Messages” table for anything (reading, writing, sorting, comparing, etc.) is extremely fast, and thus relying on a database, this is the “on-the-fly” alternative you have for non-temporary data storage (memory / sessions are still faster, but logically cannot be used to store this information).

You should worry more about creating a good ranking algorithm that provides the desired results (you offer two different systems, providing different results) and working to maximize the use of all code and the exchange of code databases.

In principle, small codes with iterative simple orders offer the fastest and most reliable solution for such situations. Example:

• A ranking function (for example, the first one offered by you or any other, built on the rules that you want), called every time a vote is given. It is written in the corresponding column (s) in the "Messages" table (the simpler the query, the better: you can create it as complex as you want, but try to rely on PHP rather than query).

• Each time a comparison between messages is required, the “Messages” table is read by a simple SELECT, sorting the entries by ranking (you may have different “rating columns” (for example, “up-votes”, down votes, further considerations); but it’s better to have one with final rating).

Answer 3

I'm not sure if it’s possible with your DB and Schema, however, do you think you need to write UDF for custom sorting?

A post from stackoverflow talks about how to do this here.