稳定/可重复的随机排序(MySQL，Rails) [英] Stable/repeatable random sort (MySQL, Rails)

问题描述

我想对ActiveRecord模型的随机排序列表(来自MySQL数据库的行)进行分页.

I'd like to paginate through a randomly sorted list of ActiveRecord models (rows from MySQL database).

但是，这种随机化需要在每个会话的基础上持续进行，以便其他访问该网站的人也会收到随机的，可分页的记录列表.

However, this randomization needs to persist on a per-session basis, so that other people that visit the website also receive a random, paginate-able list of records.

让我们说有足够多的实体(成千上万个)在会话或cookie中存储随机排序的ID值太大，因此我必须以其他方式(MySQL，文件等)暂时将其持久化

Let's say there are enough entities (tens of thousands) that storing the randomly sorted ID values in either the session or a cookie is too large, so I must temporarily persist it in some other way (MySQL, file, etc.).

最初，我以为我可以基于会话ID和页面ID创建一个函数(返回该页面的对象ID)，但是由于MySQL中的对象ID值不是连续的(存在间隙)，所以似乎下降了除了我正在戳它.令人高兴的是，它不需要/只需很少的存储空间，但是缺点是实现起来可能很复杂，而且可能会占用大量CPU.

Initially I thought I could create a function based on the session ID and the page ID (returning the object IDs for that page) however since the object ID values in MySQL are not sequential (there are gaps), that seemed to fall apart as I was poking at it. The nice thing is that it would require no/minimal storage but the downsides are that it is likely pretty complex to implement and probably CPU intensive.

我的感觉是我应该创建一个相交表，诸如:

My feeling is I should create an intersection table, something like:

random_sorts( sort_id, created_at, user_id NULL if guest)

random_sort_items( sort_id, item_id, position )

然后只需将"sort_id"存储在会话中.然后，我可以像往常一样对random_sorts WHERE sort_id = n ORDER BY位置限制进行分页.

And then simply store the 'sort_id' in the session. Then, I can paginate the random_sorts WHERE sort_id = n ORDER BY position LIMIT... as usual.

当然，我必须在一段时间不活动之后(基于random_sorts.created_at)将某种收割机放入其中.

Of course, I'd have to put some sort of a reaper in there to remove them after some period of inactivity (based on random_sorts.created_at).

不幸的是，随着创建新对象(和/或删除旧对象，尽管删除很少见)，我不得不使排序无效.而且，随着负载的增加，该表的大小/性能(即使已正确索引)也会下降.

Unfortunately, I'd have to invalidate the sort as new objects were created (and/or old objects being removed, although deletion is very rare). And, as load increases the size/performance of this table (even properly indexed) drops.

似乎这应该是一个已解决的问题，但是我找不到任何可以做到这一点的Rails插件...有什么想法吗?谢谢！

It seems like this ought to be a solved problem but I can't find any rails plugins that do this... Any ideas? Thanks!!

推荐答案

MySQL具有RAND函数，您可以在ORDER子句中使用它，并传递与用户会话相关的种子.

MySQL has a RAND function you can use in your ORDER clause, passing a seed tied to the user session.

按RAND(?)订购

在哪里?是会话中的种子值.这将为您提供跨请求的可重复订购.

Where ? is a seed value from the session. This will give you repeatable ordering across requests.

这篇关于稳定/可重复的随机排序(MySQL，Rails)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！