Random record in ActiveRecord

Question

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

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

 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.

Answer 1

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

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

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

Answer 2

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.

Answer 3

I'm brand new to RoR but I got this to work for me:

 def random
    @cards = Card.all.sort_by { rand }
 end

It came from:

How to randomly sort (scramble) an array in Ruby?

Answer 4

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

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.

EDIT I 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.