PHP MYSQL基于评级和时间戳的搜索

My search query runs like:

我的搜索查询运行如下:

 select * from posts p where p.post like '%test%' ORDER BY p.upvotes DESC, 
 p.unix_timestamp DESC LIMIT 20

If there are more than 20 results for the searched keyword, i find out the minimum timestamp value, store it in a hidden element and run another query to Load More results like:

如果搜索关键字的结果超过20个,我会找出最小时间戳值,将其存储在隐藏元素中并运行另一个查询以加载更多结果,如:

select * from posts p where p.post like '%test%' and p.unix_timestamp < 1360662045
ORDER BY p.upvotes DESC, p.unix_timestamp DESC LIMIT 20

Whats really happening is that my first query is ignoring (Obviously, my mistake) posts which haven't had any votes(meaning 0 votes) because of my ORDER BY p.upvotes DESC and as a result of this, i noticed that it fetched the first post in the table in the first 20 results, so the minimum timestamp becomes first post's timestamp. Now after this, if i try to fetch the next 20 results which is less than the minimum timestamp, it doesn't give anything.

真正发生的是我的第一个查询忽略了(显然,我的错误)帖子,因为我的ORDER BY p.upvotes DESC而没有任何投票(意味着0票),因此,我注意到它已经获取在前20个结果中表中的第一个帖子,因此最小时间戳成为第一个帖子的时间戳。在此之后,如果我尝试获取小于最小时间戳的接下来的20个结果,则它不会给出任何结果。

Right now, i am simply using the upvotes ordering to fetch top records. Should i be using some algorithm like Bayesian Average or some other algorithm?

现在,我只是使用upvotes命令来获取最高记录。我应该使用像贝叶斯平均值或其他算法的算法吗?

Please advise how i can improve the queries if i had to stay with current system of ordering or is there any viable and more efficient method i should be using?

如果我不得不继续使用当前的订购系统,或者我应该使用哪种可行且更有效的方法,请告知我如何改进查询?

P.S. If possible, please refer some resources about the Bayesian Average(it seems to be most used) or some other alternative?

附:如果可能,请参考一些有关贝叶斯平均值(似乎最常用)或其他替代方案的资源?

1 个解决方案

#1

Storing the timestamp when you first do a search and then using that for the next query you could use something like this:-

在您第一次搜索时存储时间戳,然后将其用于下一个查询,您可以使用以下内容: -

$sql = "SELECT * 
FROM posts p 
LEFT OUTER JOIN (SELECT post_id, COUNT(*) FROM post_ratings WHERE timestamp_rated <= $SomeTimeStoredBetweenPages GROUP BY post_id) pr ON p.id = pr.post_id 
WHERE p.post like '%test%' 
ORDER BY pr.post_ratings DESC, p.unix_timestamp 
DESC LIMIT ".(($PageNo - 1) * 20)." 20";

This is very much an example as I have no real idea of you table structures. Also not sure if you just have a row for each up vote, or whether there are down votes to take account of as well.

这是一个很好的例子,因为我对你的表格结构一无所知。也不确定你是否只为每一次投票都有一行,或者是否有投票也要考虑到。

#1