将排序算法应用于数据库查询[关闭]

Question

I want to make another list with "What's Hot" like reddit.

I found this topic where it explains how their sorting algorithm works

First I want to ask, if it is legal to use their algorithm?

And if yes, how would I apply it to PHP database query. Do I need to SELECT all posts first and then sort it?

function hot($ups, $downs, $date) {
    $s = $ups - $downs;
    $order = log(max(abs(s), 1), 10);
    if(s > 0) {
        $sign = 1;
    } elseif(s < 0) {
        $sign = -1;
    } else {
        $sign = 0;
    }
    $date = new DateTime($date);
    $seconds = $date->format('U');
    return round($order + $sign * $seconds / 45000, 7);
}

this is what I get when I convert it to PHP.

Answer 1

Assuming your ups and downs columns are called ups and downs, then something like:

ORDER BY ROUND(
    ( LOG10(
          GREATEST(
              ABS(`ups` - `downs`), 
              1
          )
      ) + 
      SIGN(`ups` - `downs`) *
      UNIX_TIMESTAMP(`date_posted`)  / 45000
    ),
    7
)

It might be a better idea to use this formula to create a calculated column in your select list, and then order by that column

EDIT

Example of a calculated column:

SELECT `ups`,
       `downs`,
       `posted_date`,
       ROUND(
           ( LOG10(
               GREATEST(
                   ABS(`ups` - `downs`), 
                   1
               )
             ) + 
             SIGN(`ups` - `downs`) *
             UNIX_TIMESTAMP(`date_posted`)  / 45000
           ),
           7
       ) AS hotness
  FROM `posts`

So hotness is your calculated column