Ruby-on-rails ActiveRecord 中的随机记录

Random record in ActiveRecord

提问by jyunderwood

I'm in need of getting a random record from a table via ActiveRecord. I've followed the example from Jamis Buck from 2006.

我需要通过 ActiveRecord 从表中获取随机记录。我遵循了2006 年 Jamis Buck的例子。

However, I've also come across another way via a Google search (can't attribute with a link due to new user restrictions):

但是，我还通过 Google 搜索遇到了另一种方式（由于新用户限制，无法使用链接进行归因）：

 rand_id = rand(Model.count)
 rand_record = Model.first(:conditions => ["id >= ?", rand_id])

I'm curious how others on here have done it or if anyone knows what way would be more efficient.

我很好奇这里的其他人是如何做到的，或者是否有人知道哪种方法更有效。

采纳答案by Toby Hede

I haven't found an ideal way to do this without at least two queries.

如果没有至少两个查询，我还没有找到一种理想的方法来做到这一点。

The following uses a randomly generated number (up to the current record count) as an offset.

以下使用随机生成的数字（最多为当前记录计数）作为偏移量。

offset = rand(Model.count)

# Rails 4
rand_record = Model.offset(offset).first

# Rails 3
rand_record = Model.first(:offset => offset)

To be honest, I've just been using ORDER BY RAND() or RANDOM() (depending on the database). It's not a performance issue if you don't have a performance issue.

老实说，我一直在使用 ORDER BY RAND() 或 RANDOM() （取决于数据库）。如果您没有性能问题，这不是性能问题。

回答by Mohamad

Rails 6

导轨 6

As stated by Jason in the comments, in Rails 6, non-attribute argumentsare not allowed. You must wrap the value in an Arel.sql()statement.

正如 Jason 在评论中所说，在 Rails 6 中，不允许使用非属性参数。您必须将值包装在Arel.sql()语句中。

Model.order(Arel.sql('RANDOM()')).first

Rails 5, 4

导轨 5、4

In Rails 4and 5, using Postgresqlor SQLite, using RANDOM():

在Rails 4和5 中，使用Postgresql或SQLite，使用RANDOM()：

Model.order('RANDOM()').first

Presumably the same would work for MySQLwith RAND()

想必同样会为工作的MySQL与RAND()

Model.order('RAND()').first

This is about 2.5 timesfasterthan the approach in the accepted answer.

这比接受的答案中的方法快约 2.5 倍。

Caveat: This is slow for large datasets with millions of records, so you might want to add a limitclause.

警告：这对于具有数百万条记录的大型数据集来说很慢，因此您可能需要添加一个limit子句。

回答by semanticart

Your example code will start to behave inaccurately once records are deleted (it will unfairly favor items with lower ids)

一旦记录被删除，您的示例代码将开始表现不准确（它会不公平地偏爱具有较低 id 的项目）

You're probably better off using the random methods within your database. These vary depending on which DB you're using, but :order => "RAND()" works for mysql and :order => "RANDOM()" works for postgres

您最好使用数据库中的随机方法。这些因您使用的数据库而异，但 :order => "RAND()" 适用于 mysql，而 :order => "RANDOM()" 适用于 postgres

Model.first(:order => "RANDOM()") # postgres example

回答by dkam

Benchmarking these two methods on MySQL 5.1.49, Ruby 1.9.2p180 on a products table with +5million records:

在 MySQL 5.1.49、Ruby 1.9.2p180 上对具有 +500 万条记录的产品表对这两种方法进行基准测试：

def random1
  rand_id = rand(Product.count)
  rand_record = Product.first(:conditions => [ "id >= ?", rand_id])
end

def random2
  if (c = Product.count) != 0
    Product.find(:first, :offset =>rand(c))
  end
end

n = 10
Benchmark.bm(7) do |x|
  x.report("next id:") { n.times {|i| random1 } }
  x.report("offset:")  { n.times {|i| random2 } }
end


             user     system      total        real
next id:  0.040000   0.000000   0.040000 (  0.225149)
offset :  0.020000   0.000000   0.020000 ( 35.234383)

Offset in MySQL appears to be much slower.

MySQL 中的偏移量似乎要慢得多。

EDITI also tried

编辑我也试过

Product.first(:order => "RAND()")

But I had to kill it after ~60 seconds. MySQL was "Copying to tmp table on disk". That's not going to work.

但我不得不在大约 60 秒后杀死它。MySQL 是“复制到磁盘上的 tmp 表”。那是行不通的。

回答by Niels B.

It doesn't have to be that hard.

它不必那么难。

ids = Model.pluck(:id)
random_model = Model.find(ids.sample)

pluckreturns an array of all the id's in the table. The samplemethod on the array, returns a random id from the array.

pluck返回表中所有 id 的数组。在sample该阵列上的方法，则返回从所述阵列的随机ID。

This should perform well, with equal probability of selection and support for tables with deleted rows. You can even mix it with constraints.

这应该表现良好，具有相同的选择概率和支持删除行的表。您甚至可以将其与约束混合使用。

User.where(favorite_day: "Friday").pluck(:id)

And thereby pick a random user who likes fridays rather than just any user.

从而选择一个喜欢星期五的随机用户而不是任何用户。

回答by Ryan Atallah

It is not advised that that you use this solution, but if for some reason you reallywant to randomly select a record while only making one database query, you could use the samplemethod from the Ruby Array class, which allows you to select a random item from an array.

不建议您使用此解决方案，但如果由于某种原因您真的想随机选择一条记录，而只进行一次数据库查询，则可以使用Ruby Array 类中的sample方法，该方法允许您选择随机项从一个数组。

Model.all.sample

This method requires only database query, but it significantly slower than alternatives like Model.offset(rand(Model.count)).firstwhich require two database queries, though the latter is still preferred.

这种方法只需要数据库查询，但它比Model.offset(rand(Model.count)).first需要两个数据库查询的替代方法要慢得多，尽管后者仍然是首选。

回答by spilliton

I made a rails 3 gem to handle this:

我制作了一个 rails 3 gem 来处理这个问题：

https://github.com/spilliton/randumb

https://github.com/spilliton/randumb

It allows you do do stuff like this:

它允许你做这样的事情：

Model.where(:column => "value").random(10)

回答by Knotty66

I use this so often from the console I extend ActiveRecord in an initializer - Rails 4 example:

我经常从控制台使用它，我在初始化程序中扩展了 ActiveRecord - Rails 4 示例：

class ActiveRecord::Base
  def self.random
    self.limit(1).offset(rand(self.count)).first
  end
end

I can then call Foo.randomto bring back a random record.

然后我可以打电话Foo.random带回随机记录。

回答by Thomas Klemm

One query in Postgres:

Postgres 中的一个查询：

User.order('RANDOM()').limit(3).to_sql # Postgres example
=> "SELECT "users".* FROM "users" ORDER BY RANDOM() LIMIT 3"

Using an offset, two queries:

使用偏移量，两个查询：

offset = rand(User.count) # returns an integer between 0 and (User.count - 1)
Model.offset(offset).limit(1)

回答by Sam

Reading all of these did not give me a lot of confidence about which of these would work best in my particular situation with Rails 5 and MySQL/Maria 5.5. So I tested some of the answers on ~ 65000 records, and have two take aways:

阅读所有这些并没有给我很大的信心，即在我使用 Rails 5 和 MySQL/Maria 5.5 的特定情况下，哪些最能发挥作用。所以我测试了大约 65000 条记录的一些答案，并有两个收获：

1. RAND() with a limitis a clear winner.
2. Do not use pluck+ sample.

1. 带有 a 的 RAND()limit显然是赢家。
2. 不要使用pluck+ sample。

def random1
  Model.find(rand((Model.last.id + 1)))
end

def random2
  Model.order("RAND()").limit(1)
end

def random3
  Model.pluck(:id).sample
end

n = 100
Benchmark.bm(7) do |x|
  x.report("find:")    { n.times {|i| random1 } }
  x.report("order:")   { n.times {|i| random2 } }
  x.report("pluck:")   { n.times {|i| random3 } }
end

              user     system      total        real
find:     0.090000   0.000000   0.090000 (  0.127585)
order:    0.000000   0.000000   0.000000 (  0.002095)
pluck:    6.150000   0.000000   6.150000 (  8.292074)

This answer synthesizes, validates and updates Mohamed's answer, as well as Nami WANG's comment on the same and Florian Pilz's comment on the accepted answer - please send up votes to them!

此答案综合、验证并更新了Mohamed 的答案，以及 Nami WANG 对此的评论和 Florian Pilz 对已接受答案的评论 -请向他们投票！