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

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

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

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

Answer 2

I'm actually using the code from Jamis Buck's example.

class Story < ActiveRecord::Base
  # random fuctionality snippet
  def self.find(*args)
    if args.first.to_s == "random"
      ids = connection.select_all("SELECT id FROM stories")
      super(ids[rand(ids.length)]["id"].to_i)
    else
      super
    end
  end
  # end of random snippet
end

Then access via:

Story.find(:random)

I'm just curious of other ways people have done this as there seems to be efficiency debates or db agnostic problems with most implementations.

Answer 3

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)
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.