Ruby-on-rails Rails 3：获取随机记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5342270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails 3: Get Random Record
提问by Andrew
So, I've found several examples for finding a random record in Rails 2 -- the preferred method seems to be:
因此，我发现了几个在 Rails 2 中查找随机记录的示例——首选方法似乎是：
Thing.find :first, :offset => rand(Thing.count)
Being something of a newbie I'm not sure how this could be constructed using the new find syntax in Rails 3.
作为一个新手，我不确定如何使用 Rails 3 中的新 find 语法来构建它。
So, what's the "Rails 3 Way" to find a random record?
那么，查找随机记录的“Rails 3 方法”是什么？
回答by fl00r
Thing.first(:order => "RANDOM()") # For MySQL :order => "RAND()", - thanx, @DanSingerman
# Rails 3
Thing.order("RANDOM()").first
or
或者
Thing.first(:offset => rand(Thing.count))
# Rails 3
Thing.offset(rand(Thing.count)).first
Actually, in Rails 3 all examples will work. But using order RANDOMis quite slow for big tables but more sql-style
实际上，在 Rails 3 中，所有示例都可以使用。但是RANDOM对于大表使用 order很慢，但更多的是 sql-style
UPD. You can use the following trick on an indexed column (PostgreSQL syntax):
更新。您可以在索引列上使用以下技巧（PostgreSQL 语法）：
select *
from my_table
where id >= trunc(
random() * (select max(id) from my_table) + 1
)
order by id
limit 1;
回答by xlembouras
I am working on a project (Rails 3.0.15, ruby 1.9.3-p125-perf) where the db is in localhostand users table has a bit more than 100K records.
我正在开发一个项目（Rails 3.0.15，ruby 1.9.3-p125-perf），其中 db 位于localhost并且 users 表有超过100K 的记录。
Using
使用
order by RAND()
按 RAND() 排序
is quite slow
很慢
User.order("RAND(id)").first
User.order("RAND(id)").first
becomes
变成
SELECT
users.* FROMusersORDER BY RAND(id) LIMIT 1
SELECT
users.* FROMusersORDER BY RAND(id) LIMIT 1
and takes from 8to 12 secondsto respond!!
并需要8到12 秒的响应时间！！
Rails log:
铁轨日志：
User Load (11030.8ms) SELECT
users.* FROMusersORDER BY RAND() LIMIT 1
用户负载 (11030.8ms) SELECT
users.* FROMusersORDER BY RAND() LIMIT 1
from mysql's explain
来自mysql的解释
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 110165 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
You can see that no index is used (possible_keys = NULL), a temporary table is created and an extra pass is required to fetch the desired value (extra = Using temporary; Using filesort).
您可以看到没有使用索引（possible_keys = NULL），创建了一个临时表，并且需要额外的传递来获取所需的值（extra = Using temporary; Using filesort）。
On the other hand, by splitting the query in two parts and using Ruby, we have a reasonable improvement in response time.
另一方面，通过将查询分成两部分并使用 Ruby，我们在响应时间上有了合理的改进。
users = User.scoped.select(:id);nil
User.find( users.first( Random.rand( users.length )).last )
(;nil for console use)
(;nil 用于控制台)
Rails log:
铁轨日志：
User Load (25.2ms) SELECT id FROM
usersUser Load (0.2ms) SELECTusers.* FROMusersWHEREusers.id= 106854 LIMIT 1
用户负载 (25.2ms) SELECT id FROM
usersUser Load (0.2ms) SELECTusers.* FROMusersWHEREusers.id= 106854 限制 1
and mysql's explain proves why:
和 mysql 的解释证明了原因：
+----+-------------+-------+-------+---------------+--------------------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------------------+---------+------+--------+-------------+
| 1 | SIMPLE | users | index | NULL | index_users_on_user_type | 2 | NULL | 110165 | Using index |
+----+-------------+-------+-------+---------------+--------------------------+---------+------+--------+-------------+
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
we can now use only indexes and the primary key and do the job about 500 times faster!
我们现在可以只使用索引和主键，并且可以快 500 倍的速度完成这项工作！
UPDATE:
更新：
as pointed out by icantbecool in comments the above solution has a flaw if there are deleted records in the table.
正如 icantbecool 在评论中指出的那样，如果表中存在已删除的记录，则上述解决方案存在缺陷。
A workaround in that can be
一个解决方法可以是
users_count = User.count
User.scoped.limit(1).offset(rand(users_count)).first
which translates to two queries
这转化为两个查询
SELECT COUNT(*) FROM `users`
SELECT `users`.* FROM `users` LIMIT 1 OFFSET 148794
and runs in about 500ms.
并在大约 500 毫秒内运行。
回答by icantbecool
If using Postgres
如果使用 Postgres
User.limit(5).order("RANDOM()")
If using MySQL
如果使用 MySQL
User.limit(5).order("RAND()")
In both instances you're selecting 5 records randomly from the Users table. Here is the actual SQL query in displayed in the console.
在这两种情况下，您都是从用户表中随机选择 5 条记录。这是控制台中显示的实际 SQL 查询。
SELECT * FROM users ORDER BY RANDOM() LIMIT 5
回答by spilliton
I made a rails 3 gem for doing this that performs better on large tables and allows you to chain relations and scopes:
我为此制作了一个 rails 3 gem，它在大表上表现更好，并允许您链接关系和范围：
https://github.com/spilliton/randumb
https://github.com/spilliton/randumb
(edit): The default behavior of my gem basically uses the same approach as above now, but you have the option to use the old way if you want :)
（编辑）：我的 gem 的默认行为现在基本上使用与上面相同的方法，但是如果需要，您可以选择使用旧方法:)
回答by fivedigit
Many of the answers posted actually won't perform well on rather large tables (1+ million rows). Random ordering quickly takes a few seconds, and doing a count on the table also takes quite long.
发布的许多答案实际上在相当大的表（1+ 百万行）上表现不佳。快速随机排序需要几秒钟，在桌子上进行计数也需要很长时间。
A solution that works well for me in this situation is to use RANDOM()with a where condition:
在这种情况下对我来说效果很好的解决方案是使用RANDOM()where 条件：
Thing.where('RANDOM() >= 0.9').take
On a table with over a million rows, this query generally takes less than 2ms.
在超过一百万行的表上，此查询通常需要不到 2 毫秒。
回答by Tim Kretschmer
here we go
开始了
rails way
轨道方式
#in your initializer
module ActiveRecord
class Base
def self.random
if (c = count) != 0
find(:first, :offset =>rand(c))
end
end
end
end
usage
用法
Model.random #returns single random object
or the second thought is
或者第二个想法是
module ActiveRecord
class Base
def self.random
order("RAND()")
end
end
end
usage:
用法：
Model.random #returns shuffled collection
回答by Dan Kohn
The Ruby method for randomly picking an item from a list is sample. Wanting to create an efficient samplefor ActiveRecord, and based on the previous answers, I used:
从列表中随机选择项目的 Ruby 方法是sample. 想要sample为 ActiveRecord创建一个高效的，并根据之前的答案，我使用了：
module ActiveRecord
class Base
def self.sample
offset(rand(size)).first
end
end
end
I put this in lib/ext/sample.rband then load it with this in config/initializers/monkey_patches.rb:
我把lib/ext/sample.rb它放进去，然后用这个载入它config/initializers/monkey_patches.rb：
Dir[Rails.root.join('lib/ext/*.rb')].each { |file| require file }
回答by Hishalv
This was very useful to me however i needed a bit more flexibility, so this is what i did:
这对我非常有用，但是我需要更多的灵活性，所以这就是我所做的：
Case1: Finding one random recordsource:trevor turk site
Add this to Thing.rb model
案例 1：查找一个随机记录来源：trevor turk 站点
将此添加到 Thing.rb 模型
def self.random
ids = connection.select_all("SELECT id FROM things")
find(ids[rand(ids.length)]["id"].to_i) unless ids.blank?
end
then in your controller you can call something like this
然后在你的控制器中你可以调用这样的东西
@thing = Thing.random
Case2: Finding multiple random records(no repeats)source:can't remember
I needed to find 10 random records with no repeats so this is what i found worked
In your controller:
案例 2：查找多条随机记录（无重复）来源：不记得
我需要找到 10 条无重复的随机记录，所以这就是我
在您的控制器中发现的工作：
thing_ids = Thing.find( :all, :select => 'id' ).map( &:id )
@things = Thing.find( (1..10).map { thing_ids.delete_at( thing_ids.size * rand ) } )
This will find 10 random records, however it is worth mentioning that if the database is particularly large(millions of records), this would not be ideal, and performance will be hampered. Is will perform well up to a few thousand records which was sufficient for me.
这样会随机找到10条记录，不过值得一提的是，如果数据库特别大（几百万条记录），这会不太理想，性能也会受到影响。Is 将表现良好，多达几千条记录，这对我来说已经足够了。
回答by richardun
Works in Rails 5 and is DB agnostic:
在 Rails 5 中工作并且与数据库无关：
This in your controller:
这在您的控制器中：
@quotes = Quote.offset(rand(Quote.count - 3)).limit(3)
You can, of course, put this in a concern as shown here.
当然，你可以的，如把这个关注这里。
app/models/concerns/randomable.rb
应用程序/模型/关注点/randomable.rb
module Randomable
extend ActiveSupport::Concern
class_methods do
def random(the_count = 1)
records = offset(rand(count - the_count)).limit(the_count)
the_count == 1 ? records.first : records
end
end
end
then...
然后...
app/models/book.rb
应用程序/模型/book.rb
class Book < ActiveRecord::Base
include Randomable
end
Then you can use simply by doing:
然后你可以简单地使用：
Books.random
or
或者
Books.random(3)
回答by Trond
You can use sample() in ActiveRecord
您可以在 ActiveRecord 中使用 sample()
E.g.
例如
def get_random_things_for_home_page
find(:all).sample(5)
end
Source: http://thinkingeek.com/2011/07/04/easily-select-random-records-rails/
资料来源：http: //thinkingeek.com/2011/07/04/easily-select-random-records-rails/
