There are a number of different approaches for retrieving random records from the database. The two most prevalent seem to be:

1. use the DB to randomize the results (1 query)
2. retrieve a list of ids, randomize those, then retrieve those records (2 queries)

Update

If only one record is required, best method may be to take a random number from a count of the records and use offset to get the record at that number, as follows:

offset = rand(Widget.count :all)
Widget.find :first, :offset => offset

Be aware that you may encounter an error if the count returns 0.

The debate

The RandomAccessRecordsDiscussion page discusses several methods for selecting random records.

This page will focus instead on the methods themselves.

1. Select a Record by Random Offset

This approach generates a random number from within a count of the rows in the database and then finds a record offset by that number. This would appear the be the most efficient way to select one random record.

Example

offset = rand(Widget.count :all)
Widget.find :first, :offset => offset

2. Randomize with the Database

This approach retrieves the records with a single query by using the database server to sort the records randomly before returning them.

Examples (for MySQL)

@five_random_widgets = Widget.find(:all, :order => 'RAND()', :limit => 5)

@random_widget = Widget.find(:first, :order => 'RAND()')

Other Examples From here

in Postgresql and SQLite use

@random_widget = Widget.find(:first, :order => 'RANDOM()')

in Microsoft SQL Server

@random_widget = Widget.find(:first, :order => 'NEWID()')

See Link for IBM DB2, and Oracle possibilities

3. Randomize with Ruby

This approach requires two queries. The first query is to retrieve a list of all the record IDs. That list is then sorted randomly in Ruby before being used in a second query to fetch the records.

Example

The following is shamelessly lifted from this email by Jemery Kemper

module Enumerable
  def random_sample(n)
    sort_by { rand }.slice(0, n)
  end
end

class Question < ActiveRecord::Base
  SELECT_IDS_BY_AREA_AND_SCORE = "select #{primary_key} from #{table_name} where area_id=? and score=?".freeze

  def self.random_sample(area_id, score, n = 3)
    area_id = area_id.id if area_id.is_a?(Area)
    find(find_by_sql([SELECT_IDS_BY_AREA_AND_SCORE, area_id, score]).random_sample(n).map { |q| q.id })
  end
end

Calling find with an array of ids will generate 1 "select * from questions where id in (?)" instead of N "select * from questions where id = ?".

---

The above code has been encapulated into a simple module named RandomSample that extends ActiveRecord with a random_sample method.

category: Howto