如何排序像黑客新闻 [英] How To Sort Like Hacker News

问题描述

我正在尝试将一个插件编程到bbPress（开放源代码论坛软件），它将类似于Hacker News（ http ：//news.ycombinator.com/ ）。

I am trying to program a plugin to bbPress (the open source forum software) that will work similar to Hacker News (http://news.ycombinator.com/).

具体来说，我想排序论坛线程的顺序（bbPress称之为主题）使用以下算法：

Specifically, I want to sort the order of forum-threads (bbPress calls them "topics") using the following algorithm:

sort_value = (p - 1) / (t + 2)^1.5
where p = total votes for each topic from users
t = time since submission of each topic in hours

我想通过这个计算的sort_value使用MySQL对主题进行排序。

I would like to be able to sort topics by this calculated sort_value using MySQL.

主题中的相关字段表看起来像这样：

topic_id            bigint(20)
topic_start_time    datetime

这是在空中，但我认为会有另一个表存储用户的个人投票，所以我们将能够知道用户是否已投票。另外一张表将会存储每个主题的当前投票。也许这个表中会有另一个字段存储最新计算的sort_Value？

This is up in the air, but I was thinking there will be another table that stores individual votes by users so we'll be able to know whether a user has voted already. And another table will store the current vote-totals for each topic. Maybe there will be another field in that table storing the latest calculated sort_Value?

为了准确100％，sort_value应该在每个 后更新新投票。不过，这会给数据库服务器添加太多的负载，特别是如果我们尝试更新所有主题。如果我们必须，我们可以通过仅计算最后X个主题的sort_value限制数据集。我们也可以通过仅定期更新sort_value（例如通过cron作业每5分钟一次）来限制负载。

To be 100% accurate, the sort_value should be updated after each new vote. This would add too much load to the database server, though, especially if we tried to update ALL the topics. If we have to, we could limit the dataset by only calculating the sort_value for the last X # of topics. We could also limit the load by only updating the sort_value periodically (e.g. every 5 minutes via a cron job).

这些快捷方式可能会使负载可以接受，但我宁愿一个更优雅的解决方案可以更好地扩展。

These shortcuts might make the load acceptable, but I would prefer a more elegant solution that could scale better.

你将如何构建这个？ ： - ）

How would you structure this? :-)

推荐答案

好的，这是我的想法。我将首先创建一个 old_table ，其中包含X行主题和sort_value字段。

OK, this is my idea. I'll start by creating an old_table that has X rows of topics with a sort_value field.

我想在单个表上避免大量UPDATE语句，因此我将定期用新计算的表替换旧表。据我所知，MySQL不支持替换表语法，所以每Y分钟，通过cron，我将创建一个名为 new_sort_value 。然后我会执行这个命令序列：

I want to avoid tons of UPDATE statements on a single table, so I'll periodically replace the old table with a freshly calculated table. As far as I'm aware, MySQL does not support a "replace table" syntax, so every Y minutes, via cron, I'll create an updated version of this table called new_sort_value. Then I'll do this sequence of commands:

• DROP old_table


• RENAME new_table to old_table

• DROP old_table
• RENAME new_table to old_table

这是否似乎是一种有效的方法？

Does this seem like a valid approach?

这篇关于如何排序像黑客新闻的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！